How to calculate YTD of Previous Year in Spotfire

[Ankit Mane]

I am trying to compare the YTD total or revenue for current fiscal year v/s the YTD total of the previous fiscal year on a KPI chart visual

For instance , if the current fiscal year starts in Jan 2020 and we are in April, I am able to calculate the YTD for the FY 2020 (Jan 2020 to April 2020) using the YTD funtion available.

What I am not able to accomplish is, to calculate the YTD total for the previous year 2019 . ie. Jan 2019 to April 2019.

Please advise.

[Shashank Gurnalkar]

It would be better to use a function that calculates End of the month.

So if you want to compare sum([Revenue]) for the YTD between two years, you can use this expression for the current year.

Case

When [date_column] >= Date(2020,1,1) and [date_column] = Date(2019,1,1) and [date_column]

[Ankit Mane]

Thank you for the revert Shashank..

 

Here is what I was able to achieve.......for the current FY YTD :

 

Sum([ACT_REVENUE]) THEN Sum([Value]) OVER (Intersect(AllPrevious([Axis.X]),NavigatePeriod([Axis.X],"Year",0,0))) 

 

and for Previous year YTD : (I had to create a calculated column and mark the correspoind months from the prev year as "FY19")

 

Sum(CASE  WHEN [Fin_Year]="FY19" THEN [ACT_REVENUE] END) OVER (Intersect(AllPrevious([Axis.X]),NavigatePeriod([Axis.X],"Year",-1,0)))

 

The problem with this approach is I wil have to manually change this every month.

 

Do you think the previous year expression can also be calculated using somethin like parallel period..