Color by expressions - How to show greater than average

[#1]

Hi,

I would like to have conditional formatting in my QlikSense bar chart so that when the output is significantly greater than the average, it is a distinct color.

I have a bar graph with # of invoices processed by week. I included a line chart with the average invoices processed by week with the expression: Count([Invoice #])/Count([Billed week])

For the color by expression in the bar chart, I tried

if(Count("Invoice #") > (Count([Invoice #])/Count([Billed week])*1.25)

  ,rgb(192,0,0)

  ,rgb(0,0,192) 

  )

This results in every bar being red, as it doesn't look at the unique count for that week. If I change the second part of the formula to a # instead of the expression, for example

if(Count("Invoice #") > 5,

  ,rgb(192,0,0)

  ,rgb(0,0,192) 

  )

works with any weeks with more than 5 invoices being red. Any suggestions to update the first part of the formula to only look at the count of unique billing weeks?

Thanks,

Dave

[maximiliano_vel]

What dimensions do you have on your chart?

[#3]

Dimension is billed week

[maximiliano_vel]

Since I dont have a sample data, here is an approximation of what you need

[#5]

I tried using the expression in the file you attached but am not having any success. I've moved the data into a new file which is uploaded below, please let me know if that helps shed some light on what I'm doing.

[maximiliano_vel]

Hi, I took the liberty on changing the formula for your average since checking the data I notice that when you count the number of Invoice over the number of Billed Week, i.e week 1 has 5 invoices then the formula result would be

[Invoice #]/[Billed Weeek]*1.25 = 5/5*1.25=1.25

So what I did was calculate the total avg of invoices per week

Pls see attached file