Problem to color by dimension

[linhnguyen]

Hello everybody,

I have a question about color by dimension. I have a combo chart with x axis: minutes to complete a job, and y-axis: count(jobs). I want to color one bar which has the minute = avg of delivery time.  I typed the formula in color by dimension (minute). The problem is that if I assign a fixed number, I got the right color code as the photo.

=if(Minutes= 14,'red','rgb(70,124,175)')

But if I change the fixed number to avg(deliverd_date-started_date) => Qlik doesn't understand and turn color in grey.

Could anybody help me?

Thank you so much,

[lorenzoconforti · Accepted solution]

can you share the dashboard?

[linhnguyen]

Hello,

Thank you for your help. I need help to color the minute = avg delivery time.

The calculation that I used:

=if(Minutes =
floor(avg(if( (CanceledDate-RequestDate)*24*60>0 and match(ErrorCode,'NULL') and isnull(PickedDate-RequestDate),(CanceledDate-RequestDate)*24*60,(DeliveredDate-RequestDate)*24*60)))
,'red','rgb(70,124,175)')

[lorenzoconforti · Accepted solution]

try this:

=if(Minutes =
floor(avg(total if( (CanceledDate-RequestDate)*24*60>0 and match(ErrorCode,'NULL') and isnull(PickedDate-RequestDate),(CanceledDate-RequestDate)*24*60,(DeliveredDate-RequestDate)*24*60)))
,'red','rgb(70,124,175)')

[linhnguyen]

Perfect, it works. Thank you so much.