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

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

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

nitish

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

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

 

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.

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.

Mathanbabu

Hi @david,

Thanks for your valuable information.

Thanks & regards,
Mathan