Color dimensions when aggr function for dimensions was used

[Anonymous]

hi all,

I have a bar chart, where the dimensions are created with the following aggr function

=aggr(if([Comment age]>30,'over 30 dd',if([Comment age]<=30 and [Comment age]>20,'from 21 to 30 dd',if([Comment age]>10 and [Comment age]<=20,'from 11 to 20 dd',if([Comment age]>5 and [Comment age]<=10,'from 6 to 10 dd',if([Comment age]<=5,'less than 5 dd'))))),[Comment age])

basically, it clusters "comments" (my item) aging by a set of rules.

Now, I want to color each bar chart according to its cluster, e.g. I need 'less than 5 dd' bar to be dark green, 'from 6 to 10 dd' bar to be light green, and so on.

Do I need to custom color by dimension or by expression? If it's the first case, I don't have a dimension to relate, if it's the second, I simply don't know which formula is best to use.

could you kindly suggest me a way to proceed?

thanks in advance,

Alia

[Gysbert_Wassenaar]

It's the second and basically the expression you posted. Instead of 'over 30 dd' etc strings you specify which color should be used. Something like:

=if([Comment age]>30, Green() ,if([Comment age]<=30 and [Comment age]>20, lightgreen() ,if([Comment age]>10 and [Comment age]<=20, rgb(123,123,123),  if([Comment age]>5 and [Comment age]<=10, rgb(56,56,56) ,if([Comment age]<=5,rgb(78,78,78))))))

Change the green(), lightgreen() and the rgb(...)'s  to whatever colors you want.

[Anonymous]

Hi, thanks for the quick answer, unfortunately it seems that it's not working.

I set the color as "by expression" and typed the following - there should be no mistakes in its syntax

=if([Comment age]>30,rgb(255,0,0),if([Comment age]<=30 and [Comment age]>20,rgb(255,128,0),if([Comment age]>10 and [Comment age]<=20,rgb(255,255,0),if([Comment age]>5 and [Comment age]<=10,rgb(0,255,0),if([Comment age]<=5,rgb(0,128,0))))))

however, the bar chart stays all in grey.

My guess is that [comment age] is not recognized as dimension of the chart, because its dimensions were created by the formula I wrote in the first post...

do you know other ways to work around this?

[Gysbert_Wassenaar]

Could you post a small Qlik Sense app that show the problem?

[Anonymous]

could you please explain how to post a .qvf? 🙂

I get the following message "The contents of the attachment doesn't match its file type." and I read in the community that I need to find the advanced editor but it seems that I don't have it

[Gysbert_Wassenaar]

The advanced editor you read about was for the previous version of the community. It's not relevant now. Just click the Reply button. You should see a big text area where you can enter your reply and just below the bottom left of the text area there's a Choose Files button.  You should be able to use that to upload a .qvf file.

[Anonymous]

hi, I get this error message when I try to upload a .qvf app...

"Sfoglia.." is the Choose Files button

[Gysbert_Wassenaar]

Ok, weird. It's not you, happens to me as well. Try to zip it first.

[Anonymous]

here, thanks, please find it attached 🙂

[Gysbert_Wassenaar · Accepted solution]

Ok, the color expression needs the aggr function too. 

=aggr(if([Comment age]>30,rgb(255,0,0),
    if([Comment age]<=30 and [Comment age]>20,rgb(255,128,0),
       if([Comment age]>10 and [Comment age]<=20,rgb(255,255,0),
           if([Comment age]>5 and [Comment age]<=10,rgb(0,255,0),
             if([Comment age]<=5,rgb(0,128,0)))))),[Comment age])