Ratio yearmonth to last yearmonth

[Koen_D]

Hi Qlik experts,

I have data containing dates and sales, and a master calendar with YTD field.

I want to show a ratio of this years sales compared to last year sales in a table containing a yearmonth field. The solution I tried is this:

=Sum({<Year = {$=Max(Year)}>}Sales) / =Sum({<Year = {$=Max(Year) - 1}>}Sales)

But it shows no solution, I think because of the set analysis on both 2024 as 2023. Do you expers have a solution for this?

[Kushal_Chawda]

@Koen_D  If you have year month field in your table, you may need to try above() function. Make sure that you

=Sum(Sales) / 
above(total Sum({<YearMonth>}Amount),12)*avg(1)

[Kushal_Chawda · Accepted solution]

@Koen_D  if you make selection on month and sort your YearMonth just above is not enough so try below

=Sum(Sales)/aggr(above(total Sum({<YearMonth>}Sales),12),(YearMonth,(NUMERIC,ASCENDING)))

[Koen_D]

Works like a charm! Thanks Kushal!