Conditional Formatting According to Partial Sum/Avg

[#1]

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!

[antoniotiman]

Hi Stefano,

why in Background Color  You divide by NoOfRows() ?

[Anonymous]

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())

[#4]

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.

[#5]

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.

[Anonymous]

try to use rangesum which includes NULL values

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