topic Re: Pivot Table Comparison in QlikView

Pivot Table Comparison

Thu, 21 Jan 2016 10:00:46 GMT

Would like to ask a Question regarding Pivot Table

I am wanting to compare the amount of 12 months together.
if Sum August > July, background is Green

if Sum August = July, background is Yellow

if Sum August < July, background is Red

But i am not sure how to achieve it.

The code i put in the expression background color is :

if(sum({<Month={'Aug'}>}BAL) > (sum({<Month={'Jul'}>}BAL)), rgb(0,255,0), rgb(255,255,255))

This is the problem i am facing now, the whole Column Of Aug is Green if i put this code in my background color of my expression. Besides that, what is the best way to get better loading time because using too much if else is too heavy as well.

Re: Pivot Table Comparison

vikasmahajan — Thu, 21 Jan 2016 11:04:24 GMT

you can use visual cues for the same.

Vikas

Re: Pivot Table Comparison

vikasmahajan — Thu, 21 Jan 2016 11:08:30 GMT

use color in background of visual cues.

Re: Pivot Table Comparison

abirami_palanis — Thu, 21 Jan 2016 12:56:56 GMT

Hi,

Try this,

Go to Backgroud color and type below expression.

if(sum({$<Month={'Aug'}>}BAL) > sum({$<Month={'Jul'}>}BAL),green(),if(sum({$<Month={'Aug'}>}BAL) = sum({$<Month={'Jul'}>}BAL),Yellow(),red()))

Re: Pivot Table Comparison

maniram23 — Thu, 21 Jan 2016 13:32:19 GMT

Hi,

select the Expression Plus it will show the 'Background color' option .

Than you can write your expression by using color .

Re: Pivot Table Comparison

Fri, 22 Jan 2016 01:59:28 GMT

Yes i know it can be change in Visual Cues, but what do i type in the Upper & the Lower?

As i am using Month as a Dimension in Pivot Table, i would only have to put one expression which is Sum(BAL) and that is it.

But how should i compare each month? As in what expression should i use in which allows it to compare each month, for example Feb will compare with Jan, March compare with Feb, April compare with March and so on.

I heard we should use Above or Previous, but i am not sure how.

Re: Pivot Table Comparison

Fri, 22 Jan 2016 02:03:26 GMT

It doesn't work that way as if i used your expression in the background colour, it will make the whole Column in Jul Red & Aug Green, it does not compare the value in August or July.

Re: Pivot Table Comparison

Fri, 22 Jan 2016 02:06:05 GMT

yes i know there is that function on that, but i am using Month as a dimension so it will prompt out 12 month from Jan to Dec, and in the expression i just add Sum(BAL) which will list out all the sum of each month, and I am having problems thinking of an expression to write which will compare each previous month to see if the Sum of (BAL) is higher / lower / same.