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))
)
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment