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

edited March 25 in Modeling


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

Best Answer

  • PujithaB
    Answer ✓

    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.


  • @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:

  • @beatrizduenas

    Did the above help?

  • Thank you so much, it did help