bar chart query

[hopkinsc]

Hi all,

I need some help with a calculated dimension in bar chart

[Anonymous]

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

[hopkinsc]

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]

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

I will check that shortly

Sent from my iPhone

[ramoncova06]

yes it will

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

[stigchel · Accepted solution]

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]

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]

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]

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]

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