Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

vchuprina
New Contributor II

Pivot table with calculation year to year

Hi,

I'm trying to create pivot table with following structure, but can't correctly calculate Evolution.

In my model I have weight and height and based on it I calculate index as Heigth/Weigth.

Evolution should be counted by comparing current year with previous (2017 vs 2016, 2016 vs 2015, 2015 vs 2014)

Can someone help with set expression.

   

Year2014201520162017
Weigth81879198
Height179181184190
Index0,450,480,490,52
Evolution 6,222,894,29
1 Solution

Accepted Solutions
MVP
MVP

Re: Pivot table with calculation year to year

Try this:

=((Weight/Height)/Before(Weight/Height) - 1) * 100

Capture.PNG

8 Replies
MVP
MVP

Re: Pivot table with calculation year to year

Is Weight and Height part of a single dimension or are they dimensions itself? Would you be able to share raw data behind this to help you better?

vchuprina
New Contributor II

Re: Pivot table with calculation year to year

Weight and Height are dimensions itself

MVP
MVP

Re: Pivot table with calculation year to year

Would you be able to share sample data or a sample qvw file?

vchuprina
New Contributor II

Re: Pivot table with calculation year to year

Hi Sunny,

Please find sample qvw in attachment

ahaahaaha
Honored Contributor

Re: Pivot table with calculation year to year

What exactly is compared when calculating the Evolution ? You can give an example of the calculation of Evolution for at least one year?

MVP
MVP

Re: Pivot table with calculation year to year

Try this:

=((Weight/Height)/Before(Weight/Height) - 1) * 100

Capture.PNG

vchuprina
New Contributor II

Re: Pivot table with calculation year to year

Thank you. I didn't know about before function

MVP
MVP

Re: Pivot table with calculation year to year

You can check out after and before here:

Missing Manual - Before() and After()