Operations pivot table in Qlik Sense

[alexvalqlik]

Good, for a query:

in a pivot table, I have several measures for every year, I need to do the following:

in expressions using:

if (ColumnNo()=1, Column(1) + Column(2))

this expression returns me the correct result because both columns belong to the same year, but need to subtract 1 column a year against the same column but another year

For better detail I attached image of the pivot table.

Referring to the image:

The Column "Variación (2016)" = Column Accumulated (2016) - Column Accumulated (2015)

The Column "Variación (2015)" = Column Accumulated (2015) - Column Accumulated (2014)

The Column "Variación (2014)" = Column Accumulated (2014) - Column Accumulated (2013)

Thank you for your prompt help.

[Gysbert_Wassenaar]

I don't see 2013 as a year in your pivot table so I assume you only show 2016, 2015 and 2014. But if you still want to show the variation between 2014 and 2013 then you'll need something like an AsOf table to associate each year with its previous year. See this blog post: The As-Of Table. In your case you need a very simple AsOf table that looks something like this:

AsOf:

LOAD * INLINE [

Year_AsOf, Year, YearDiff

2014, 2014, 0

2014, 2013, 1

2015, 2015, 0

2015, 2014, 1

2016, 2016, 0

2016, 2015, 1

];

[alexvalqlik]

Gysbert

Thank you for responding, I do not understand how to relate your suggestion with the pivot table.

If we see the picture you send, I need to calculate the "variation" of each of the years column with an expression example:

(Sum({<AÑO={'=((AÑO))'}, MES={'<=$(=Max([MES]))'}>} Venta_Neta)

-

(Sum({<AÑO={'=((AÑO)-1)'}, MES={'<=$(=Max([MES]))'}>} Venta_Neta)

/

Sum({<AÑO={'=((AÑO))'}, MES={'<=$(=Max([MES]))'}>} Venta_Neta)

[Gysbert_Wassenaar]

Quite. And I was suggesting how you go about that. But if you already have a working solution feel free to use that

[alexvalqlik]

Sorry Gysbert,

The translator does his job well.

I want to explain myself better:

I do not understand how to relate your next suggestion with the pivot table:

ASOF:

* LOAD ONLINE [

Year_AsOf, Year, YearDiff

2014, 2014, 0

2014, 2013 1

2015, 2015, 0

2015, 2014 1

2016, 2016, 0

2016, 2015 1

];

and this expression is just an idea of what I need to do, although I did not work:

(Sum ({<YEAR = {'= ((year)) "}, {MONTH =' <= $ (= Max ([Mo])) '}>} Venta_Neta)

-

(Sum ({<YEAR = {'= ((NAO) -1) "}, {MONTH =' <= $ (= Max ([Mo])) '}>} Venta_Neta)

/

Sum ({<YEAR = {'= ((year)) "}, {MONTH =' <= $ (= Max ([Mo])) '}>} Venta_Neta)

Thanks and regards.

[Gysbert_Wassenaar]

The reason the expression you posted does not work is that the set of a set analysis expression is calculated at the chart level, not the row (or in this case column) level of the dimension. That's why I propose an AsOf table. Perhaps this document helps understanding the problem and the possible solutions: Calculating rolling n-period totals, averages or other aggregations