topic Re: Pivot Table and ColorMix - Please Help in QlikView

Pivot Table and ColorMix - Please Help

prees959 — Wed, 23 Sep 2020 19:21:15 GMT

HI,

I have a relatively simple Pivot table with two Dimensions : Unit (down the left) and Product as Column Headings.

My expression is a simple percentage : (sum(ordered) - sum(provided )) / sum(provided)

One thing to note is that there are some negative percentages (which is expected). What I would like to do is Color the expression values to something like the following :

Is it possible to colorize the expression as Red for the highest values (eg 100% and 167% as above moving towards green to 0%) with amber for the middle values.

Im trying to show the movement away from 0% for both negative and positive values. Also, would colormix merge the colors into a gradient?

Any help gratefully received.

Phil

Re: Pivot Table and ColorMix - Please Help

Kushal_Chawda — Wed, 23 Sep 2020 21:43:13 GMT

@prees959 try below expression in background color expression

=ColorMix1 (fabs((sum(ordered) - sum(provided )) / sum(provided)) / $(=max(aggr(fabs((sum(ordered) - sum(provided )) / sum(provided)),Unit,Product))), ARGB(255, 255, 0, 0), ARGB(255, 0, 255, 0))

Re: Pivot Table and ColorMix - Please Help

prees959 — Wed, 23 Sep 2020 22:50:10 GMT

hi @Kushal_Chawda - thanks so much for your reply. It seems to work but for some reason its missing out some of the higher values :

any ideas?

Thanks

Re: Pivot Table and ColorMix - Please Help

Kushal_Chawda — Wed, 23 Sep 2020 23:15:12 GMT

@prees959 or check the colormix wizard in background expression. Put below expression for colormix wizard and then you can set the upper, lower and intermediate settings and set the colors

fabs((sum(ordered)  - sum(provided )) / sum(provided))

https://www.youtube.com/watch?v=YasIe3VUJrg