topic Color by expressions - How to show greater than average in App Development

Color by expressions - How to show greater than average

Sat, 16 Nov 2024 11:05:50 GMT

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

Re: Color by expressions - How to show greater than average

maximiliano_vel — Fri, 14 Aug 2015 15:21:32 GMT

What dimensions do you have on your chart?

Re: Color by expressions - How to show greater than average

Fri, 14 Aug 2015 15:25:39 GMT

Dimension is billed week

Re: Color by expressions - How to show greater than average

maximiliano_vel — Fri, 14 Aug 2015 17:40:03 GMT

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

Re: Color by expressions - How to show greater than average

Fri, 14 Aug 2015 18:37:59 GMT

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.

Re: Color by expressions - How to show greater than average

maximiliano_vel — Sat, 15 Aug 2015 00:39:46 GMT

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