Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I need some help with a calculated dimension in bar chart
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
Instead of using "dimension limits, you can use rank() function in calculated dimension. Just use "Show total" on the dimension limits.
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
Apologies. I didn't see the example you attached.
I will check that shortly
Sent from my iPhone
yes it will
here is another option if you want to make it work with your drilldown
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
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)
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?
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)
Thanks everyone for your help. i think i now have it working