Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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)