topic Re: Highlight fields in pivot table in QlikView

Highlight fields in pivot table

Thu, 25 Sep 2014 10:05:41 GMT

Hi guys,

I have a pivot table with some numerical values and I need some formula in order to highlight them either green or red. What I need to do is to calculate if the numbers in the rows under the first one (the first row stays in place and it's always green) are either +- 5% of the number in the first row that's above it. How do I do this?

Thanks,

Adrian

PS: I have the free version, so I'm not able to open any file.

Re: Highlight fields in pivot table

Thu, 25 Sep 2014 10:13:07 GMT

Hi Adrian,

In the properties of Pivot table, there is a tab called "Visual Cues".

You can apply colors to the expression here according to your requirement.

Hope this helps.

Thanks

Sabal

Re: Highlight fields in pivot table

Thu, 25 Sep 2014 10:18:53 GMT

Hi,

I'm aware of this, I don't know how to get the first number from every column.

Re: Highlight fields in pivot table

crusader_ — Thu, 25 Sep 2014 10:20:25 GMT

Hi,

I suppose you need to make calculated backgound of your cell in pivot table.

If it is, Chart-> Properties->Expressions-> "Expand + in expression"->Backgound color->Definition

If(condition, red(), green())

Hope this helps.

Andrei

Re: Highlight fields in pivot table

rubenmarin — Thu, 25 Sep 2014 10:20:50 GMT

Hi Adrian,

In the expressions tab you can expand the expression and there you can set the colors for background and font.

You can use something like:

If (YourExpression>Above(YourExpression)*1.05, green(),

If (YourExpression<Above(YourExpression)*0.95, red()))

Re: Highlight fields in pivot table

sundarakumar — Thu, 25 Sep 2014 10:20:56 GMT

in background color part try to add a expression like

if(sum(sales)>(above(sum(sales))+above(sum(sales))*.05), green(),

sum(sales)<(above(sum(sales))+above(sum(sales))*.05),red()))

-Sundar

Re: Highlight fields in pivot table

Thu, 25 Sep 2014 10:25:04 GMT

Ok, this is a bit helpful, but what I'm trying to do is this:

Let's say this is a column: 100, 101, 106, 102, 98

100 is always green, 101 is green (because it's in the range 100 and 100+5%), 106 is red(not in the range), 102 is green(in the range), 98 is green (it's in 100 and 100-5%)

Is this a bit more clear? I might have not explained myself good in the first place.

Thanks

Re: Highlight fields in pivot table

sundarakumar — Thu, 25 Sep 2014 10:28:00 GMT

If u always want to refer to the first row then u can replace above() with first().. This will always look for 100in the eg that u have specified..

-Sundar

Re: Highlight fields in pivot table

rubenmarin — Thu, 25 Sep 2014 10:32:18 GMT

The maybe the expression should be like this:

If (YourExpression<Top(YourExpression)*1.05 and YourExpression>Top(YourExpression)*0.95, green(), red())

Re: Highlight fields in pivot table

munna1317 — Thu, 25 Sep 2014 10:34:53 GMT

use backgroung color or text color in expresion

Re: Highlight fields in pivot table

Thu, 16 Oct 2014 09:27:43 GMT

Hi,

If I'm using Top(expression) it doesn't take into account any number after the second one. Basically, if the first number in the column is 100, the second one is 95, let's say, 95 will be green, which is good, but the rest of number will be red, regardless of their value..

Adrian

Re: Re: Highlight fields in pivot table

rubenmarin — Thu, 16 Oct 2014 09:49:15 GMT

Hi, Check attachment, in simple example it works, maybe your table has more dimensions and need to use a TOTAL or something, can you post a sample?

Or you can use directly Top(expression) to check what value is used to compare, it will give you a hint of how to solve.

Re: Re: Highlight fields in pivot table

Thu, 16 Oct 2014 12:25:49 GMT

Unfortunately, I cannot open attachments for I have the free version

What formulas did you use?

Re: Re: Highlight fields in pivot table

rubenmarin — Thu, 16 Oct 2014 12:39:38 GMT

The same formula, but it works in a simple example:

If (Value<Top(Value)*1.05 and Value>Top(Value)*0.95, green(), red())