5 Replies Latest reply: Aug 14, 2015 8:39 PM by Maximiliano Velez RSS

    Color by expressions - How to show greater than average

    David Shippee

      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