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 SortSum_ValueVersionsTime Period NewQuarterCurrent MonthQTDYTD1500Actuals1/1/2023101121000Actuals2/1/2023111130Actuals3/1/2023100040Actuals4/1/2023200050Actuals5/1/2023200060Actuals6/1/2023200070Actuals7/1/2023300080Actuals8/1/2023300090Actuals9/1/20233000100Actuals10/1/20234000110Actuals11/1/20234000120Actuals12/1/20234000

Current Month, QTD, YTD Flag

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

Tips and Tricks

Accepted answers

Christina_H

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

Current month and quarter.yxmd

All comments

Christina_H

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

Current month and quarter.yxmd

Raj

Sol

QTD.yxmd

ShankerV

Hi @itsmeanuj

One way of doing this.

Many thanks

Shanker V

Current Month, QTD, YTD Flag.yxmd

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703