topic Re: Conditional Formatting According to Partial Sum/Avg in QlikView

Conditional Formatting According to Partial Sum/Avg

Wed, 08 Jul 2015 22:05:53 GMT

Hello guys,

I'm trying to format a table according to its totals (in my case it is an average total), i. e., I want to show a different color for when the value is higher or lower than the average.

I am basically struggling with the average calculation. I can't always return the correct average of my expression, when I use simple functions as sum, it appears to be correct, however when I use other kinds of logics the average returned isn't the same as the table shows.

I would like to know if there is a function to return the exact partial or if there is another solution to this.

I attached an example to elucidate the question.

Thank you very much for your time!

Re: Conditional Formatting According to Partial Sum/Avg

antoniotiman — Thu, 09 Jul 2015 07:29:13 GMT

Hi Stefano,

why in Background Color You divide by NoOfRows() ?

Re: Conditional Formatting According to Partial Sum/Avg

Anonymous — Thu, 09 Jul 2015 07:43:57 GMT

I have changed your Background Color Definition to this and it works (I tried it with Revenue)

for the other expressions Change it according to this Expression:

if(sum([Revenue USD])>sum(TOTAL ( [Revenue USD]))/count(distinct total Rep_Name),lightgreen(),lightred())

Re: Conditional Formatting According to Partial Sum/Avg

Thu, 09 Jul 2015 16:06:57 GMT

Hi Antonio,

Since my table is in horizontal mode, my dimensions are still considered rows.

I want to reacreate the average total qlikview does and I believe it uses all the data, even if a representative (rep_name field in this case) doesn't have sales.

Re: Conditional Formatting According to Partial Sum/Avg

Thu, 09 Jul 2015 17:37:10 GMT

Hi Rudolf, thanks for your answer.

Both ways work well for simple calculations, the major problem is when the expression returns null values (e.g. a representative doesn't have sales goals).

Qlikview disregard these null values from the average total, whilst both our solutions put them into account and I couldn't find a way to reacreate this logic.

Re: Conditional Formatting According to Partial Sum/Avg

Anonymous — Fri, 10 Jul 2015 06:59:16 GMT

try to use rangesum which includes NULL values

I didnot check this with your application, but give it a try