topic Re: Trying to use set analysis to show baseline value in pivot in QlikView

Trying to use set analysis to show baseline value in pivot

Mon, 19 Jan 2015 20:39:52 GMT

Hi,

I'm having trouble to get my set analysis to work in a pivot tabel in Qlikview.

In my test application I have 6 companies showing EBIT values for each year starting from 2004.

In a variable (vBaseLineYear) I want to provide the baseline year and have the value of that year shown in my pivot.

However, when using this expression: sum({<Year={$(vBaseLineYear)}>} EBIT)

It will only show the value of the baseline year as follows in this example (where vBaseLineYear = 2007):

It will only calculate when it is actually the baseline year and not with the other years.

The problem seems to be not so much the variable, but rather the using of my set analysis in combination with this pivot set up.

Any advice would be greatly appreciated.

Re: Trying to use set analysis to show baseline value in pivot

rwunderlich — Mon, 19 Jan 2015 21:37:37 GMT

Your displayed results make sense to me, so I believe I may be misunderstanding your objective. For the expression "EBIT Baseline", the other years show zero because of the SET expression, which is expected. For the "EBIT" expression, the values show for the other years.

Are you trying to fold this in to one line and somehow indicate the baseline year like with a different background color?

-Rob

Re: Trying to use set analysis to show baseline value in pivot

terezagr — Mon, 19 Jan 2015 22:08:47 GMT

Hi,

try this: =Sum({$<Year={"$(vBaseLineYear)"}>}Ebit)

Re: Trying to use set analysis to show baseline value in pivot

terezagr — Mon, 19 Jan 2015 22:23:44 GMT

I think you could do it with alternate state. You would have to assign the vBaseLine alternate state 2 and the same also use within your set analysis.

Re: Trying to use set analysis to show baseline value in pivot

Tue, 20 Jan 2015 06:40:36 GMT

Hi, I've made a screenshot of what I'm trying to accomplish. I might not have been clear since the results make sense to you:

I want the chosen baselineyear value for each company put underneath the EBIT value of each year.

So right now in above example for row 'TESTCOMPANY A' it gives 0 for each column (2004, 2005, etc) except year 2007. But I want that value '37847' to be displayed in each year for 'TESTCOMPANY A'

For the other rows ('TESTCOMPANY B ... F') I want these results.

This meanshaving value:

'27603' in each 'EBIT BaseLine' column for TESTCOMPANY B

'36796,7' in each 'EBIT BaseLine' column for TESTCOMPANY C

etc.

Hope this makes my intention clear.

Thank you in advance.

Re: Trying to use set analysis to show baseline value in pivot

rwunderlich — Tue, 20 Jan 2015 20:03:07 GMT

I think that should be fairly easy. Add the Total keyword like this:

=sum({<Year={$(vBaseLineYear)}>}TOTAL<[Company Name]> EBIT)

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Trying to use set analysis to show baseline value in pivot

Tue, 20 Jan 2015 20:14:40 GMT

Whoa! That was it!

I feel weird having overlooked this, but many thanks!