topic bar chart query in QlikView

bar chart query

hopkinsc — Thu, 30 Apr 2015 17:27:48 GMT

Hi all,

I need some help with a calculated dimension in bar chart

Re: Totals in bar chart

Anonymous — Thu, 30 Apr 2015 18:17:43 GMT

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

Re: Totals in bar chart

hopkinsc — Thu, 30 Apr 2015 18:21:14 GMT

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

Re: Totals in bar chart

hopkinsc — Thu, 30 Apr 2015 18:23:02 GMT

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

I will check that shortly

Sent from my iPhone

Re: Totals in bar chart

ramoncova06 — Thu, 30 Apr 2015 18:27:00 GMT

yes it will

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

Re: Totals in bar chart

stigchel — Thu, 30 Apr 2015 18:47:25 GMT

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

Re: Totals in bar chart

hopkinsc — Thu, 30 Apr 2015 19:28:27 GMT

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),

(

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

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

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

)

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

Re: Totals in bar chart

hopkinsc — Thu, 30 Apr 2015 19:49:16 GMT

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?

Re: Totals in bar chart

stigchel — Thu, 30 Apr 2015 19:57:24 GMT

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)

Re: Totals in bar chart

hopkinsc — Thu, 30 Apr 2015 20:26:00 GMT

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