Top N % to total

[Anonymous]

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...

[Jason_Michaelides]

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

[Jason_Michaelides]

Or try this...

Sum({<Location={"=if(aggr(Rank(Sum(Dollar_Demand_Net)),Location)<=10,Location)"}>} Dollar_Demand_Net)

[Anonymous]

That if-statement returns locations, it would do no good to sum it.  Thanks though.

[Anonymous]

I had thought to try the quotes too, but sadly, that didn't work either. 

[rwunderlich]

You should be able to do this in the same chart with:

Sum(Dollar_Demand_Net) / Sum(TOTAL Dollar_Demand_Net)

-Rob

http://robwunderlich.com

[Anonymous]

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?

[rwunderlich]

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

[Anonymous]

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

[swuehl · Accepted solution]

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)