topic Re: Pivot table with set analysis? in QlikView

Pivot table with set analysis?

mr_novice — Mon, 04 Feb 2013 11:58:42 GMT

Hello all!

I have a problem that I cant solve and hope you can give me a suggestion how to solve this

I have a pivot table with dimensions Category and YearMonth. Categories are displayed as rows and YearMonth as columns so for each category there are one expression per YearMonth.

Now, the expression is just a sum of TransactionAmount. This works fine. But now I want add one expression that calculates the delta between month's.

My first expression is Sum([Month Result]) and to create a delta between ie 201202 and 201201 I do this but it doesn't work:

sum([Month Result])
-
sum({$<[YearMonthCounter]={"=$(=[YearMonthCounter]-1)"}>}[Month Result])

I use "Yearmonthcounter" to avoid problems when its the first month of the year.

This is what I want to achieve:

Category 201201 201202 201203

Amount Delta Amount Delta Amount Delta

1 1000 0 500 -500 200 -300

2 200 0 1000 800 100 -900

Any suggestions?

cristian

Re: Pivot table with set analysis?

alexandros17 — Mon, 04 Feb 2013 12:17:17 GMT

use keyword "PREVIOUS"

Re: Pivot table with set analysis?

mr_novice — Mon, 04 Feb 2013 12:23:31 GMT

Hmm you mean like this?

sum({$<[YearMonthCounter]={"=$(=previous([YearMonthCounter]))"}>}[Month Result])

alexandros17 wrote:
use keyword "PREVIOUS"

Re: Pivot table with set analysis?

alexandros17 — Mon, 04 Feb 2013 12:45:59 GMT

I'm sorry I meant BEFORE, so if Month is a dimension (on the x axis) you have

sum(Month Result) - Before(sum(Month Result))

Re: Pivot table with set analysis?

mr_novice — Mon, 04 Feb 2013 14:29:05 GMT

Great. Thank you!

What about if I want to compare this new expression with the grand total of 'before sum(MonthResult))' like this:

sum(monthResult)-before(sum(monthResult)) / before(sum(TOTAL monthResult))...This does not work...

cristian