Trying to use set analysis to show baseline value in pivot

[#1]

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.

[rwunderlich]

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

[terezagr]

Hi,

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

T.

[terezagr]

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.

T.

[#5]

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.

etc.

Hope this makes my intention clear.

Thank you in advance.

[rwunderlich · Accepted solution]

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

[#7]

Whoa! That was it!

I feel weird having overlooked this, but many thanks!