Aggregating prior year data in set analysis

[#1]

Hi Qlik Community,

I have a set of sales data that I'd like to make year-over-year comparisons. The table I'm trying to create needs to include both current year (CY) and prior year (PY) data on the same row, while using year as the dimension. Having both year's data on the same line helps in calculating differences over years.

To illustrate, it looks like this:

Year   CY_Sales PY_Sales

2014   $200         $210

2013   $210         $180

2012   $180         $170

The data includes sales information from 2011-2014, but only 2012-2014 should be shown.

I've tried using various set analysis expressions. None of them seem to work.

SUM({<Year = {$(=nYear-1)}> TOTAL Sales})

SUM({<Year = {'=Max(Year)-1'}> Sales})

or simply making column PY_Sales = below(CY_Sales), but since 2011 data is not included in the chart, PY_Sales for 2012 is 0, there is also problem when a user makes a selection, and only a single year's data is returned. So this option is not feasible.

Any ideas for solving this?

Thank you!!!

[Gysbert_Wassenaar]

The table I'm trying to create needs to include both current year (CY) and prior year (PY) data on the same row, while using year as the dimension

In that case you cannot use set analysis (well, not easily). Set analysis creates one set per chart, not per row. But there are other solutions. See this document: Calculating rolling n-period totals, averages or other aggregations

[#3]

Thank you so much for replying! I will look into the solution you mentioned.

[#4]

Hi Gysbert,

Brilliant solutions! I got it to work "partially". The problem I have now is when I use the expression below to restrict the result to only a certain kind of customers, it does not add up correctly all the time. It returns 0 for the accumulative number, if the current year's sale_amt is 0.

sum(aggr(rangesum(above(total sum({<Customer.Status={'New'}, Customer.Year=>}Customer.Sale_Amt),0,2)),Customer.Year))