topic Re: Pivot Table Dimension (Above expression) in QlikView

Pivot Table Dimension (Above expression)

Fri, 22 Jan 2016 06:37:07 GMT

Pic 1

Pic 2

Just a question, when i change the Dimension of Month From Row to Column the Background Colour Expression is wrong.

The results in Pic 1 i posted is what i wanted, but i want to remain the correct colouring but the Month would be display on the Column instead of Row.

My expression for the Background Colour :

if(sum(BAL) > Above(sum(AL)), rgb(51,255,51),

if(sum(BAL) = Above(sum(BAL)), rgb(255,255,102),

if(sum(BAL) < Above(sum(BAL)),rgb(255,51,51), rgb(255,255,255))))

Re: Pivot Table Dimension (Above expression)

swuehl — Sat, 23 Jan 2016 14:51:25 GMT

If you pivot Month dimension to the top, you need to change Above() to Before():

if(sum(BAL) > Before(sum(AL)), rgb(51,255,51),

if(sum(BAL) = Before(sum(BAL)), rgb(255,255,102),

if(sum(BAL) < Before(sum(BAL)),rgb(255,51,51), rgb(255,255,255))))

Re: Pivot Table Dimension (Above expression)

Tue, 26 Jan 2016 03:43:02 GMT

Thanks a lot, it works.

With this formula does Jan 2014 compare with Dec 2013 itself too?

Re: Pivot Table Dimension (Above expression)

swuehl — Tue, 26 Jan 2016 17:21:22 GMT

Only if you have both dimension values listed in your table, Dec 2013 left from Jan 2014.

Re: Pivot Table Dimension (Above expression)

Wed, 27 Jan 2016 02:31:31 GMT

So if i have a list box to choose every year then mostly Jan will not be able to compare with the previous year Dec, did i get what you said correctly?

Re: Pivot Table Dimension (Above expression)

swuehl — Wed, 27 Jan 2016 10:19:38 GMT

Not if you just use above expression.

However, you can achieve what I think you want by using advanced aggregation, something along the lines Elif Tutuk described here (in the very last example):

Accumulative Sums

sum( aggr( rangesum( above( sum( {$<Month>} Sales),0,12) ),Month))

Take care, that when using chart inter record functions inside the aggr(), you need to use above() / below(), even when your Month dimension is pivoted in the chart.

You also need to take care that the Load order of the relevant aggr() dimensions (Month in this case) is sorted chronological.