topic Re: Highlighting values in Pivot in QlikView

Highlighting values in Pivot

Sunil_Kenth — Mon, 15 Sep 2014 18:33:44 GMT

Hi,

I am trying to figure out how i can highlight values in a pivot table if they are lower than the previous month.

My dimensions are:

Customer

OrderMonth (derived from OrderDate in the script)

OrderMonth is Pivoted and displayed across the top of the table.

Expression:

sum(OrderTotal)

I would like to highlight where ordertotals are declining.

Thanks,

Sunil

Re: Highlighting values in Pivot

ecolomer — Mon, 15 Sep 2014 18:38:55 GMT

You can put an expresion in de Background Color in expression:

if(Order Month < Order Month -1, red())

To put this expression, you need open the + sign in the name of expression

Re: Highlighting values in Pivot

Sunil_Kenth — Mon, 15 Sep 2014 18:41:54 GMT

I used the 'before' function and this seemed to work:

if(sum([Order Total]) < before(sum([Order Total])),LightBlue())

Thanks for looking.

Re: Highlighting values in Pivot

cspencer3 — Mon, 15 Sep 2014 18:43:04 GMT

Could you change the background color or text color of the expression based on the criteria you mentioned. I would possibly make a flag to flag records as current month and previous month. Then you could use this flag in your analysis so that it doesn't get too crazy in the Set Analysis.

Therefore you could do some logic such as

if(sum({<MonthFlag = {'Current''}>}OrderTotal) < sum({<MonthFlag = {'Previous'}>}OrderTotal, rgb(157, 7, 7)

and place this in your text color/background color property of the target expression.

You may also want to use Numbers as your flags as they will be quicker in calculations than strings. Food for thought.

Re: Highlighting values in Pivot

cspencer3 — Mon, 15 Sep 2014 18:44:25 GMT

Sorry, i was too slow to type. When i replied i found out that you found the answer.

Re: Highlighting values in Pivot

Sunil_Kenth — Mon, 15 Sep 2014 18:46:35 GMT

Thanks for taking the time to reply anyway.

Regards,

Sunil

Re: Highlighting values in Pivot

Mon, 15 Sep 2014 18:52:48 GMT

Go to the "Expressions" background color and add this

=if(sum(LineSalesAmount) < before( sum(LineSalesAmount) ),red(),green())

Modify this as required.

Re: Highlighting values in Pivot

Mon, 15 Sep 2014 18:59:24 GMT

Sorry, As my window was opened, I didn't saw that this is already answered..