Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Conditional Formatting According to Partial Sum/Avg

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!

5 Replies
antoniotiman
Master III
Master III

Hi Stefano,

why in Background Color  You divide by NoOfRows() ?

Anonymous
Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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
Not applicable
Author

try to use rangesum which includes NULL values

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