topic Total spend for top 10 suppliers in each category in QlikView

Total spend for top 10 suppliers in each category

Tue, 05 Apr 2011 16:02:09 GMT

I have the following expression which provides me with the total spend if a supplier is in the top 10 for its category. This works fine when in a straight table and the suppliers and categories are shown as dimensions.

=IF(aggr(rank(sum([NET_Amount])),Category,Supplier_Key)<=10,Sum([NET_Amount]))

I now want to use this information but in a new straight table that only has the Category dimension and shows an expression for total spend for the top 10 suppliers in that category. I'm not sure how to do this?

Do I need to use set analysis?

Thanks in anticipation,

Alex Botten

Total spend for top 10 suppliers in each category

Wed, 06 Apr 2011 13:40:32 GMT

BOUNCE

Someone in the community must know how to help me with this one

Total spend for top 10 suppliers in each category

Wed, 06 Apr 2011 16:49:27 GMT

I think it is possible but I don't have the final answer yet.

You would need the Set Analysis to be based on the Key(Id) of your observations (and not on the Category, Supplier) as the Set Analysis 'filter the records once for the whole chart (table or graph)'

I still don't get the whole total, but here is what I get so far:

=Sum({$<Id = {"=Id = IF(aggr(rank(sum([NET_Amount])),Category,Supplier_Key)<=10,Id)"}>}NET_Amount)

Hope others will contribute.

Total spend for top 10 suppliers in each category

Thu, 07 Apr 2011 09:26:29 GMT

Thanks Nicolas I'll work with what you have sent and see if I make any progress.

Total spend for top 10 suppliers in each category

Thu, 07 Apr 2011 15:11:48 GMT

For Set Analysis; I always refer to these articles:

http://community.qlik.com/forums/t/33248.aspx

http://iqlik.wordpress.com/2010/08/14/the-magic-of-set-analysis-introduction/ (4 articles)

http://andpointsbeyond.com/2009/07/02/whats-possible-in-qlikview-9/

Let me know if you or someone else find the solution; I am interested.