Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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 ...)
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 ...)
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