topic Re: % of an reference point using pivot table in QlikView

% of an reference point using pivot table

yelin_nyu — Wed, 22 Jul 2015 22:31:48 GMT

im tracking performance of people overtime depending on when they initially started. i call it class

the data set is like below. the first line is saying, for people started in jan, their performance is 1000 in jan, and went down to 700 in feb.

*i simplified the problem here, in the actual app, the class is a date and goes on indefinitely. so creating individual column for each class is not a good idea.

i created a pivot table looks like this in qlikview

now i need to create another pivot table like below that evaluates each month's performance relative to the first month people joined.

how to do that?

Re: % of an reference point using pivot table

marcus_sommer — Thu, 23 Jul 2015 11:11:54 GMT

I think you will need inter-records functions like above() or before() for this, try something like this:

sum(value) / alt(before(sum(value), 1, 1), sum(value))

- Marcus

Re: % of an reference point using pivot table

yelin_nyu — Thu, 23 Jul 2015 16:02:23 GMT

before () allows me to look at the previous column, but it doesn't let me reference the first column.