topic Set Analysis: Top 10 records fitting a particular criteria in QlikView

Set Analysis: Top 10 records fitting a particular criteria

Wed, 24 May 2017 10:33:28 GMT

Hi,

I'm currently trying to write a QlikSense if statement to fill the following criteria - the top 10 suppliers who supply over 10% of our current stock, and with a returns rate of above 35%.

My current syntax is as follows, however this does not work:

=aggr(if(if((Sum(Product.StockToday)/sum(Total Product.StockToday)) >.01 and (Sum(Sales.ReturnsLineTotalGBP)/sum(Sales.GrossSellingPriceGBP)) <.35 ,
Rank(aggr((Sum(Sales.ReturnsLineTotalGBP)/sum(Sales.GrossSellingPriceGBP))), Product.Current_Supplier,4))<11, Product.Current_Supplier),Product.Current_Supplier)

Any tips of solutions would be greatly appreciated - thanks!

Re: Set Analysis: Top 10 records fitting a particular criteria

sunny_talwar — Wed, 24 May 2017 15:09:41 GMT

Would you be able to provide some sample data and also the expected output based on the data provided

Re: Set Analysis: Top 10 records fitting a particular criteria

Wed, 24 May 2017 15:21:10 GMT

Hi Sunny,

Thanks for your response! Unfortunately, I cannot provide a data sample due to the sensitivity of the information. However, the output expected would be to see suppliers who provide over 10% of total stock ( sum(Total Product.StockToday))) and have a returns rate of over 35% , then show the top 10 ranked by returns rate descending(Sum(Sales.ReturnsLineTotalGBP)/sum(Sales.GrossSellingPriceGBP).

Re: Set Analysis: Top 10 records fitting a particular criteria

sunny_talwar — Wed, 24 May 2017 15:33:09 GMT

Dummy or mocked up data?Preparing examples for Upload - Reduction and Data Scrambling