How to sum the values of specific days of the month. Ex. sum the values of all the mondays.

Hello,

I have a requirement for the client to find the ratio of the days of the week over the value of the month.

To do this I need to sum for example the value of all the Mondays of the month and then divided by the value of the month.

I though about making line items with just the value of that day of the week that I can find with weekday() function. But I don't know if this will be the best option since I will need 7 line items, one for each day and then doing an if else clause with 7 conditions.

Any light on how to address this? Find an example below

*We are using a 445 calendar on the model

Thank you

Accepted answers

PujithaB

Hi beatrizduenas
I think we can try with the following approach.
Create Weekday List and then,
\Identify each day with Weekday Number Such as Monday as 1, Tuesday as 2 ….and then map with Weekday List
Now in final module just sum by Weekday list having weekday List as Dimension
This is another option apart from Line items
Choosing the option will depends on how you wish to display on UX and underlying calculations.

Let me if this will work out.

All comments

PujithaB

rob_marshall

@beatrizduenas

@PujithaB is on the correct path, but since you are playing with Native Time, you will have issues summing the data from daily up to monthly due to the different blocks of Time.

Create a Weekday List

This is the missing piece, you have to create a "customized/fake" Day list which is best done using a SYS Time - Day module. Make sure you have a good code, I used YYYYMMDD.

to create this list:

Create a SYS Module using the above list and create these line items:

Create a module, using the above list to get the daily data into a module that you can do sums on. The formula would

Lastly, create a module using Native Time (at the Month level) dimensionalized by your Weekday list

Notice, Stage Monthly data is only dimensionalized by Time at the Day level.

The Ratio formula for the Time summary for Final is:

For a result of:

rob_marshall

@beatrizduenas

Did the above help?

beatrizduenas

Thank you so much, it did help

Quick Links

Can you help?

Unanswered questions

Hi Team , I am unable to login to the Anaplan portal using my credential.
Can someone help me i am unable to login anaplan portal using my credentials
Finding History for List Item Creation and changes
Dear Hive Mind, Do we have a way to get the history of a particular List Item, from its creation into a model up until its removal ? I am trying to chase when / how an item has been added into a model and how / when it would have been deleted and then recreated with the same code. Is there a pattern (manual or API) to get…
Action fails via cloudworks but works manually
I have an action in a process which is getting failed since 2 days when run via CloudWorks with below error . No issues when run manually . Same error occurs when run manually but as a warning "Import complete but with failures" This action is running since almost a year without any issue . No change has been made to…