So very close -- only thing missing was a couple closing parens!
| eval Delta=null()
| foreach 2* [| eval Delta=if(isnull(Delta),'<<FIELD>>' ,(('<<FIELD>>'-'Delta')*100/'Delta')) ]
TYVM!!! I'm happy for another 979 years!
... View more
Old thread, I know, but here's an answer for anyone looking for this in the future.
Our fiscal year starts in October. I use the following in our DateTime Picker (defined in User Interface > Time Ranges 😞
This Fiscal Year: earliest=-3q@y+3q latest=+q@y+3q
Previous Fiscal Year: earliest=-y-3q@y+3q latest=-y+q@y+3q
Haven't extensively tested it through a full year's range, but at least on paper it looks good, at least for those instances where the fiscal year boundary corresponds to a quarterly boundary. For This Fiscal Year, the calculation basically is to back up 3 quarters from the current date, snap to the year, and add 3 quarters to it; the latest is to add one quarter to the current date, get the year, and add 3 quarters to that. Previous FY just backs up a year before doing the same calculation.
e.g. #1: Jan 1, 2020, earliest = (Jan 1, 2020 - 3 quarters) = Mar 1, 2019 = snap to 2019 + 3 quarters = Oct 2019
e.g. #2: Sep 30, 2020, earliest = (Sep 30, 2020 - 3 quarters) = Dec 30, 2019 = snap to 2019 + 3 quarters = Oct 2019
Other examples and customizing for your fiscal year boundaries are left to the reader. But do let me know if you find a flaw with this approach!
... View more