topic Top N % to total in QlikView

Top N % to total

Anonymous — Thu, 05 Apr 2012 15:08:00 GMT

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

Top N % to total

Jason_Michaelides — Thu, 05 Apr 2012 16:52:13 GMT

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

Top N % to total

Jason_Michaelides — Thu, 05 Apr 2012 16:55:35 GMT

Or try this...

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

Top N % to total

Anonymous — Thu, 05 Apr 2012 16:55:41 GMT

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

Top N % to total

Anonymous — Thu, 05 Apr 2012 16:57:29 GMT

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

Top N % to total

rwunderlich — Thu, 05 Apr 2012 17:18:55 GMT

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

Top N % to total

Anonymous — Thu, 05 Apr 2012 18:59:00 GMT

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?

Top N % to total

rwunderlich — Thu, 05 Apr 2012 20:07:28 GMT

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

Top N % to total

Anonymous — Thu, 05 Apr 2012 20:14:10 GMT

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

Top N % to total

swuehl — Thu, 05 Apr 2012 22:20:50 GMT

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)

Top N % to total

Jason_Michaelides — Fri, 06 Apr 2012 06:45:29 GMT

Brilliant 🙂 Must be right!

Top N % to total

Anonymous — Fri, 06 Apr 2012 13:31:02 GMT

Yup, Brilliant! Thank you swuehl! I'm still surprised you don't need to aggr rank by Location but you don't. Your solution works great.

Re: Top N % to total

swuehl — Sat, 07 Apr 2012 13:30:04 GMT

No, you don't need to explicitely aggregate over the field on which you want to perform an advanced search to filter sets.

Please compare with the example from the Help file:

sum( {$<Customer = {“=Sum({1<Year = {2007}>} Sales ) > 1000000”}>} Sales )

Same here, Sales is aggregated per Customer, hence the field name of the element_set is defining the scope of the advanced search aggregation functions.

Regards,

Stefan

Re: Top N % to total

Jagveer — Sun, 26 Apr 2020 17:03:22 GMT

HI,

I have been searching for something similar but the expression that i have is aggregated based on year so when i use your solution it says nested aggregation not allowed. Could you please help.

This is my top 10 data based on YoY change

The YoY change is calculated column

sum({<Year={2020}>} [Revenue CAD_QTD])- sum({<Year={2019}>} [Revenue CAD_QTD])

Account	2019	2020	YoY Change	% Change
	$1,898.78	$2,550.76	$651.98	34.34%
C1	$710.07	$874.23	$164.15	23.12%
C5	$70.20	$200.74	$130.53	185.93%
C4	$261.18	$380.68	$119.50	45.75%
C3	$306.82	$410.19	$103.36	33.69%
C2	$488.39	$556.05	$67.66	13.85%
C9	$25.73	$68.06	$42.33	164.49%
C23	$5.05	$15.00	$9.94	196.86%
C25	$4.15	$10.25	$6.10	147.06%
C22	$11.05	$15.40	$4.35	39.39%
C19	$16.12	$20.17	$4.05	25.12%