topic Re: YTD in script in App Development

YTD in script

Lazar1 — Tue, 27 Jun 2023 15:13:30 GMT

Hi, anyone knows how to create YTD dimension field with 'Y' and 'N' values in the script?
Basically, I want to add a filter so when choosing 'Y' filter out the current year up to today and all previous year up to the same date.

Example:

Year	Month	YTD
2022	Jan	Y
2022	Feb	Y
2022	Mar	Y
2022	Apr	Y
2022	May	Y
2022	Jun	Y
2022	Jul	N
2022	Aug	N
2022	Sep	N
2022	Oct	N
2022	Nov	N
2022	Dec	N
2023	Jan	Y
2023	Feb	Y
2023	Mar	Y
2023	Apr	Y
2023	May	Y
2023	Jun	Y

Re: YTD in script

Lazar1 — Tue, 27 Jun 2023 15:44:12 GMT

I kinda solved it with this code, but If anyone has another and better idea, you are welcome!

if(YearToDate(sale_creation_date, -3),'Y',
if(YearToDate(sale_creation_date, -2),'Y',
if(YearToDate(sale_creation_date, -1),'Y',
if(YearToDate(sale_creation_date),'Y', 'N')))) as YTD2

Basically YearToDate function only applies to 1 year at a time, so I need to manually check each previous year and assign Y (-3 looks 3 years back, -2 two years back ...)

Re: YTD in script

BrunPierre — Tue, 27 Jun 2023 15:53:47 GMT

Like this.

If( Date#(01 & '/' & Date(Date#(Month,'MMM'),'M') & '/' & Year) - YearStart(Date#(01 & '/' & Date(Date#(Month,'MMM'),'M') & '/' & Year)) <= Today() - YearStart(Today()), 'Y', 'N' ) as IsInYTD