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

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.

Accepted answers

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

All comments

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

5_Saturdays-_Forum_43312.docx

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 :

Time Dimension in Pages
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

Quick Links

Can you help?

Unanswered questions

Using Anaplan for Managing Custom Product Configurations (Example: Designer Dolls)
We’ve recently started using Anaplan to model and manage our supply chain and production planning for a niche ecommerce business that sells custom-designed dolls — each made-to-order with specific features, materials, and delivery lead times. A few interesting challenges we faced: SKU Explosion: Each doll can be customized…
OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…