Search This Blog

Monday, November 14, 2016

Qlikview , Sum sale of top N customer of each month


Using RANK function to check ranking of summary

Case1: Display rank with customer name for each month
Dimension1 = FactAll.MTH_YEAR
Dimension2 = Rank (Calculated Dim)
Dimension3 = CustomerName (Calculated Dim)

Dim2 :Rank
=Aggr(rank(sum(FactAll.Fee)),FactAll.MTH_YEAR ,Account.CustomerName)

Dim3 :CustomerName
=IF(AGGR(RANK(SUM(FactAll.Fee)),FactAll.MTH_YEAR, Account.CustomerName)<= 100 , Account.CustomerName )






















About order , Just order by rank 



Case2: Display only summary of top 100 customer fee
Dimension1 = FactAll.MTH_YEAR
Expression = SumFee

Measure Expression :
=SUM(
IF(Aggr(rank(sum(FactAll.Fee)),FactAll.MTH_YEAR ,Account.CustomerName)<=100,
Aggr( sum(FactAll.Fee) ,FactAll.MTH_YEAR ,Account.CustomerName))
)


No comments:

Post a Comment