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

beatrizduenas
beatrizduenas
edited March 25 in Modeling

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

image.png

*We are using a 445 calendar on the model

Thank you

Best Answer

  • PujithaB
    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.

Answers

  • rob_marshall
    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

    image.png

    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.

    image.png
    image.png

    to create this list:

    image.png

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

    image.png
    image.png

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

    image.png

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

    image.png

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

    The Ratio formula for the Time summary for Final is:

    image.png

    For a result of:

    image.png

  • rob_marshall
    rob_marshall

    @beatrizduenas

    Did the above help?

  • beatrizduenas
    beatrizduenas

    Thank you so much, it did help

Categories