Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results 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
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)

13 Replies
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

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.

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?

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

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)

Community Browser