Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

stascher
Contributor

Comparing values across all columns in pivot table.

Hi,

I've got the following pivot table:

pivottable.png

What I'd like to do is...

...for each row, compare the "%of group affected" value for each Group and if the difference between the highest and lowest "% of group affected" is >= 50, set the background color of the highest "%of group affected" in the row to yellow.

I already know how to calculate "%of group affected" and how to identify when a cell contains the highest or lowest value by using hrank().

What I need is an expression I can use when specifying a  cell's background color that will calculate the difference between highest and lowest "%of group affected" in the row.  I tried using the rangemax() and rangemin() with before() and after() functions but I couldn't get them to work.

Thanks,
Steve

1 Reply
stascher
Contributor

Re: Comparing values across all columns in pivot table.

well this works, but I wonder if there's a better way....

=rangemax(before(count(distinct subject)/Aggr(NODISTINCT Count(DISTINCT subject), group),0,ColumnNo()),after(count(distinct subject)/Aggr(NODISTINCT Count(DISTINCT subject), group),0,noofcolumns()-columnno()+1))

-rangemin(before(count(distinct subject)/Aggr(NODISTINCT Count(DISTINCT subject), group),0,ColumnNo()),after(count(distinct subject)/Aggr(NODISTINCT Count(DISTINCT subject), group),0,noofcolumns()-columnno()+1))

Community Browser