Find 5 Saturdays in a month using Date functions and not Calendar type

[visivasa]

Hi,

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. 

[LipChean_Soh · Accepted solution]

Hi visivasa,

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'.

Thanks,

LipChean

[Nitishsingh]

Hi LipChean,

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 :

1. Time Dimension in Pages
2. Line Items in Columns.

Thanks

Nitish Singh

[visivasa]

Hi @LipChean_Soh,

Thanks for the elegant and straightforward solution!

Thanks @Aishwarya and @Mathanbabu as well for your inputs - helps me learn more about these functions.

[Aishwarya]

Hi Visivasa,

     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,

Aishwarya

[Mathanbabu]

Hi @visivasa,  

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. 

Blueprint View:

Regular View:

Kindly revert for further queries.

Thanks and regards,

Mathan                    

[DavidSmith]

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

David

[Mathanbabu]

Hi @david,

Thanks for your valuable information.

Thanks & regards,
Mathan

[visivasa]

Thanks Mathan - this is almost the same as the Excel formula I was using and thus easier for me to follow. I am unsure of the performance, in terms of space or time/calculations as I am new to Anaplan when I compare your solution to the others but I went ahead with the first solution as it is super straightforward for the other users to understand as well.