Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm looking to display the top 10 location's $ percent to total. I have a chart to display the top 10 using a dimension expression of
=if(aggr(Rank(Sum(Dollar_Demand_Net)),Location)<=10,Location)
Suppressing When Value is Null
and an expression of Sum(Dollar_Demand_Net)
However, I need to see that total value's percent to the total of ALL locations. I realize I can't get this in the same chart but hope I can get it in a text object.
The total of all locations is easy, but how do I calculate the top 10 locations in a text, object single's expression?
p.s.
This didn't work as an expression
Sum({<Location={$(=if(aggr(Rank(Sum(Dollar_Demand_Net)),Location)<=10,Location))}>} Dollar_Demand_Net)
Thoughts...
Not sure if I got your requirements correctly.
If I want the sum of Dollar_Demand_Net for the top N (say 10) Locations e.g. in a text box, I would probably just use:
=sum({<Location={"=rank(sum(Dollar_Demand_Net))<=10"}>} Dollar_Demand_Net)
and if you want the total share, something like
=sum({<Location={"=rank(sum(Dollar_Demand_Net))<=10"}>} Dollar_Demand_Net) / sum(Dollar_Demand_Net)
Can't you just wrap the whole thing in a Sum()?
=Sum(if(aggr(Rank(Sum(Dollar_Demand_Net)),Location)<=10,Location))
Stefan (Sweuhl) is your man for advanced aggregations! Hopefully he'll pick this up later if my stab in the dark doesn't work...
Jason
Or try this...
Sum({<Location={"=if(aggr(Rank(Sum(Dollar_Demand_Net)),Location)<=10,Location)"}>} Dollar_Demand_Net)
That if-statement returns locations, it would do no good to sum it. Thanks though.
I had thought to try the quotes too, but sadly, that didn't work either.
You should be able to do this in the same chart with:
Sum(Dollar_Demand_Net) / Sum(TOTAL Dollar_Demand_Net)
-Rob
You're right, that's a great way to include the values in the chart, but is there anyway to only have the final sum(top N) % to total?
The easy way is to include a total line in the chart and only display this expression on the total line. You can do that by testing the rowno().
If(rowno=0
, Sum(Dollar_Demand_Net) / Sum(TOTAL Dollar_Demand_Net)
, '' // no display on detail lines
)
If you want to get it in a text box it's just a matter of figuring out that rank set analysis expression and then dividing by Sum(TOTAL Dollar_Demand_Net).
-Rob
Thanks Rob, again, it's help for not displaying on the row level but it still consumes a column in a chart. It's that set analysis expression in a text object that I would really like to know whether or not is possible - and the correct expression
Not sure if I got your requirements correctly.
If I want the sum of Dollar_Demand_Net for the top N (say 10) Locations e.g. in a text box, I would probably just use:
=sum({<Location={"=rank(sum(Dollar_Demand_Net))<=10"}>} Dollar_Demand_Net)
and if you want the total share, something like
=sum({<Location={"=rank(sum(Dollar_Demand_Net))<=10"}>} Dollar_Demand_Net) / sum(Dollar_Demand_Net)