How to get total of Monthly Values based on Day Value

Options
MaheshDamani
MaheshDamani Member, ALL USERS, Partner, Certified Model Builder Posts: 5 New Contributor
in Anaplan Community

Hello All,

I am having an issue while creating time dimension setting. Customer requires Weekly, Monthly and Quaterly reporting. Data from transactional system will be loaded Daily.

I have tried options such as:-

1. Having 445 distribution, but the Jan 2018 will be addition of Week 1 to Week 4 which is not true addtion as Week 5 has 3 days of January and two days in February

2. Checked Week General, but how to derive monthly numbers and quarterly numbers with this time settings. All the function such as MONTHVALUE or QUARTERVALUE do not work with these settings

3. Week 4 13 periods - Customer doesnt like naming convention and P1 to P13 creates more confusion. Hence this is not an option.

 

There is no readymade setting that can be used in this context it seems. Any idea or workaround on this ?

Application will have 10 future years.

 

How to get Monthly value as true sum based on dates or days in month rather than relying on Weeks ?

 

Greetings'

Mahesh

Tagged:

Comments

  • MaheshDamani
    MaheshDamani Member, ALL USERS, Partner, Certified Model Builder Posts: 5 New Contributor

    Hello Paul,

    May be i need to explain this a bit more:-

    Customer wishes to load the data daily. Time setting used in the Application is Week General to get Weekly View. Week General doesnt give Months as autopopulated, hence how to derive the monthly numbers based on the addition of the days (not weeks). Do I need to create another list with just Months members to it ?

    Same thing goes for Quarterly results also.

     

    Now even if I change the settings of 4-4-5, the summation in the time summary will take sum of weeks rather than day. Hence the monthly level numbers are incorrect.

    Hope i am able to explain this now.

    Thanks for your reply

    Mahesh

     

     

     

     

  • MaheshDamani
    MaheshDamani Member, ALL USERS, Partner, Certified Model Builder Posts: 5 New Contributor

    Hello Sellis,

    I understand the concept, but i am having future 10 years to cover. This would need lot of mapping as for every year Day to Week mapping will be different. I understand it can be one time setup also to this once for all. How do you think to achieve this mapping, i would avoid manual ways to do this.

     

    I am trying to figure out if any of the time settings can help me to provide easy and simple solution to customer. Any idea on that lines are welcome, where I can achieve Month consolidation based on days rather weeks. I am open to use any time distribution since we are creating a new model.

     

    Thanks

    Mahesh

  • StephenEllis
    StephenEllis Member, ALL USERS, GroupMember Posts: 1 New Contributor
    Mahesh,

    I think it would be simple to model this in Excel, create a CSV and import this for the 10 years.

    However, there is a good chance you Can create this logic.

    1) Days module would need to carry Year dim. You would need to load the data here.
    - The month can be a property defined as a list against the flat months.
    - The days of the year would need to be populated for the first year, but could be calculated the following year
    - Assuming you know how to break down the weeks, this can also be automated based on the start day of the year and using simple math to define the day of week and based on if this is a Mon -Sun or a Sun-Sat. I am pretty sure getting the serial date for the 1/1 of the year vs the current day serial should allow you to calculate the day of week and therefore derive the week number.

    No question that this could be somewhat complex logic, but It can be automated.

    2) use Summary cube to look up on these properties.

    Isn't it reasonable that the user should expect some timely maintenance in a modeling application?

Categories