I am trying to create a boolean flag that will tell me if a month has 5 saturdays or not.
Can someone guide me in constructing this with date functions? Unfortunately, I cannot change my calendar settings to 4-4-5 etc because of business restrictions, so I have to calculate this manually in order to adjust and smooth our demand projections.
In Excel, we can achieve this using days in a month / 7 and then adjusting for weekday of the first day in the month, last day in the month and adding a +1/-1 accordingly to arrive at the # of saturdays in that month.
Solved! Go to Solution.
In your 'Time Admin' module, i.e. the System module with only 'Time' as its dimension, create the following line items:
1. Weekday = Weekday(Start()), Applies To = Day
2. Sat Count = If Weekday = 6 then 1 else 0, Applies to = Day
3. Sat Count by Mth = Saturday Count, Applies To = Month
Note the different 'Applies To' above.
Then you can choose what you want to do with 'Sat Count by Mth'.
We can get the same output using only first two line items as well (Just to avoid use of one extra line item), however we need to do some changes in pivot for the same.
The changes are :
Please check if the attached screenshots are useful to you. I have used the weekday function for identifying Saturdays and changed the timescale and summary method for the cumulate line item from day to month and then created a boolean in the last line item.
Thanks and regards,
I have an alternate suggestion. This could be better in terms of minimizing sparsity and is probably similar to the excel logic which you have hinted in your post. I have given below the screenshots. Please take a look at it.
Kindly revert for further queries.
Thanks and regards,
I would add, that if this is a module purely dimensioned by time, it is more efficient and easier to understand if the line items are split out
Breaking formulas up helps with calculation efficiency and in a single dimensional module such as Time Settings, the size impact should be miminal