topic Issue with set analysis in QlikView

Issue with set analysis

Tue, 13 Mar 2012 10:35:57 GMT

Hi all,

I am trying to create a chart that shows a yearly trend and for that I am using set analysis on the year:

sum({$<Year=>}_profit)

/ sum({$<Year=>}_assets)

But I have an issue with creating the correct denominator. In business terms the correct calculation is:

(Assets current year + Assets last year)/2

For the yearly trend this means:

2009: (assets09 + assets08)/2

2010: (assets10 + assets09)/2

2011: (assets11 + assets10)/2

Any ideas, how to set up the expression, so that I can show the yearly trends for above?

Thanks for your help!

Re: Issue with set analysis

jvitantonio — Tue, 13 Mar 2012 10:47:40 GMT

Use:

(sum({$<Year={'2009'}>}_assets)+sum({$<Year={'2010'}>}_assets))/2

Issue with set analysis

swuehl — Tue, 13 Mar 2012 10:51:20 GMT

If your dimension is year, try using one of the chart inter record functions, above() or below() (depending on your sort order) or before() and after() (if dimension is pivoted in a pivot table):

sum({$<Year=>}_profit)

/ (sum({$<Year=>}_assets) + above(sum({$<Year=>}_assets)) )

Issue with set analysis

Tue, 13 Mar 2012 10:58:54 GMT

Thanks!