According to date find the date range 1 Month, 3 Months, 5 Months, 5 weeks, 10 Weeks, MTD, QTD, YTD, YOY, PMTD, PQTD,PYTD

[Lakshmanudui]

Hi All,

Requirement:

I have date field, based on the date field to find the range fields in Script side.

Range field to find based on the current date .

1 Month   --- It will display the  Nov -1 2022 to Nov -30 2022

3 Months --- Nov-1-2022 to Sept-1-2022

....

Rangefield having elements --- 1 ,3,...Months, 5,10weeks , MTD, PMTD....

Exclude the current month info, 

If select the 1 Month in rangefield it will display the Nov -22 Complete Info,

If select the 3 Month in rangefield it will display the Nov -22, Oct - 22, Sept -22 Complete Info,...

Could you please help on that.

Thanks Advance.

Lakshman

[Aditya_Chitale]

Create a multiple calendars with all above required date ranges and give flags for Field names. eg. for YTD, create YTD calendar and add 'YTD' as RangeField flag. Similarly create calendars for other date ranges as well and concatenate them and link them with the fact table in your data model.

In frontend,  you can use created field RangeField from your calendar as a filter.

Alternative way is to use if condition using getfieldselections(). But I wouldn't recommend it as you will have to add same expression multiple times in different charts.

Regards,

Aditya

[vikasmahajan]

Hi ,

You can implement As-of Table  nicely explained my  Manish here in this video :

https://www.youtube.com/watch?v=Pe7FC5o13Jk

This will resolve your requirements for more periods like  MTD , PMTD  etc you need to build in as-of table itself.

Vikas