Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Set Analysis: Top 10 records fitting a particular criteria

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!

3 Replies
MVP
MVP

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

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

Not applicable

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

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

MVP
MVP

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