Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Top N % to total

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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)

View solution in original post

13 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

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
Luminary Alumni
Luminary Alumni

Or try this...

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Not applicable
Author

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

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)