Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
AWS Degraded - You may experience Community slowness, timeouts, or trouble accessing: LATEST HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

bar chart query

Hi all,

I need some help with a calculated dimension in bar chart

1 Solution

Accepted Solutions
stigchel
Partner - Master
Partner - Master

In this case two calculated dimensions with the rank function in a drilldown group and only show total checked in the dimension limits.

Example attached

View solution in original post

9 Replies
Anonymous
Not applicable

Instead of using "dimension limits, you can use rank() function in calculated dimension.  Just use "Show total" on the dimension limits.

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi. Would that make the total bar correct ? I mean will that only total the dimension values shown ?

Do you have an example of this by any chance ?

Sent from my iPhone

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Apologies. I didn't see the example you attached.

I will check that shortly

Sent from my iPhone

ramoncova06
Partner - Specialist III
Partner - Specialist III

yes it will

here is another option if you want to make it work with your drilldown

stigchel
Partner - Master
Partner - Master

In this case two calculated dimensions with the rank function in a drilldown group and only show total checked in the dimension limits.

Example attached

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi all, i am trying to use Piet's solution as this works when drilling down and it sorts correctly too. but when i tyry and use my ACTUAL expression in the Calculated dimension (instead of Sum(Value) i am getting 'Garbage after expression' error.

Can you see what i am doing wrong?

So this is what i have in the calculated drill down on 1 of the dimensions included..

=if(Aggr(Rank(Sum(IF

(

DATE(DateToday) < DATE($(vCurrentMonthWD2Date)),

ALT($(vForecast_PriorMonth),0)/$(vREPORTEDWD5_PriorMonth),

IF

(

DATE(DateToday) < DATE($(vCurrentMonthWD5Date)),

ALT($(vTotal_Month),0)/$(vForecast_Month),

ALT($(vTotal_Month),0)/$(vREPORTED_DAYFIVE_Month)

)

)))<=vLimit,vpc_ClassDesc),vpc_ClassDesc)

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi, i seem to have found the issue with the above expression, but i have now noticed that i am getting the top 3 ranked, whereas what i need is the lowest 3 not top.

eg.

Value A = 1000

Value B = 500

Value C = 450

Value D = 750

Value E = -500

Value F = 100

Value G = -350

so out of the above i need to return the following (in the same order)

Value E

Value G

Value F

I rank is giving me

Value A

Value D

Value B

does that make sense?

stigchel
Partner - Master
Partner - Master

For the bottom 3 just reverse the rank by putting a minus sign in front of the expression you are trying to rank, so in my example for group

=if(Aggr(Rank(-Sum(Value))<4,Group),Group)

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Thanks everyone for your help. i think i now have it working