Current Month, QTD, YTD Flag

[itsmeanuj]

Hi Guys,

I am looking for some help/logic in achieving Quarter, Current Month, QTD, and YTD Flags in my data. These flags are dynamic in nature and keep updating every month. e.g. we have data only till Feb (sum_value >0), hence Current month would be 1, QTD/YTD = 1 for Jan & Feb).

Time Sort	Sum_Value	Versions	Time Period New	Quarter	Current Month	QTD	YTD
1	500	Actuals	1/1/2023	1	0	1	1
2	1000	Actuals	2/1/2023	1	1	1	1
3	0	Actuals	3/1/2023	1	0	0	0
4	0	Actuals	4/1/2023	2	0	0	0
5	0	Actuals	5/1/2023	2	0	0	0
6	0	Actuals	6/1/2023	2	0	0	0
7	0	Actuals	7/1/2023	3	0	0	0
8	0	Actuals	8/1/2023	3	0	0	0
9	0	Actuals	9/1/2023	3	0	0	0
10	0	Actuals	10/1/2023	4	0	0	0
11	0	Actuals	11/1/2023	4	0	0	0
12	0	Actuals	12/1/2023	4	0	0	0

[Christina_H · Accepted solution]

This should work.  It assumes the current month is the last one with [Sum_Value]>0

[Raj]

Sol

[ShankerV]

Hi @itsmeanuj 

One way of doing this.

Many thanks

Shanker V

[itsmeanuj]

Thanks, @ShankerV  - This solution won't work when we have data for April. Using this solution QTD Flag for Jan, Feb, and Mar is also showing 1 when we have data in April. Ideally only Apr should have a QTD flag as 1 since its the beginning of the new quarter.

Thanks,

Anuj

[ShankerV]

Hi @itsmeanuj 

I have tweaked the workflow to get the desired result.

Many thanks

Shanker V