Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Hi Stefano,
why in Background Color You divide by NoOfRows() ?
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())
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.
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.
try to use rangesum which includes NULL values
I didnot check this with your application, but give it a try