Set Analysis in the Pivot table

[#1]

Hi All,

I have created a Pivot table in Qlik sense with [Fiscal Year Month], ASVP, RSD, Owner Name rows and Count(ActivityCount) and Count(FTACount) measures.  See the Pivot table picture attached. 

[Fiscal Year Month], ASVP, RSD, Owner Name, Seller Type, [ActivityCount] and  FTACount are the fields in one table. Seller Type has 3 values [Bank NSE, MSE, MMSE].  There are several Owner (Owner Name) that report to one RSD and then RSD report to a single ASVP.  So hierarchy is: ASVP on top and then RSD and then Owner. 

I want to calculate another measure (just 1 measure):  Total Tickets for all Owner, ASVP and RSD based on following criteria:

If [Seller Type]= 'Bank NSE' and Count(FTACount) >= 44 then result = 3/ month. 

If [Seller Type]= 'MSE' and Count(FTACount) >=8  then result = 3/month

If [Seller Type]= 'MMSE' and Count(FTACount) >= 4 then result = 3/month

If ASVP or RSD then add Count(FTACount) /(number of Owner for Bank *44)+ (number of Owner for MSE *8) +(number of Owner for Bank *4) .      

I have the syntax for the Owner only as follows but does not work right.  Can someone please help!

Sum({$<[Owner Name] = {"=Count(FTACount) >= Pick(Match([Seller Type],'Bank NSE','MSE','MMSE'),40,8,4)"} >} 3)

Thanks,

Monica

[sunny_talwar]

May be you need to use Aggr() function instead of set analysis

Sum(Aggr(If(Count(FTACount) >= Pick(Match([Seller Type],'Bank NSE','MSE','MMSE'), 40, 8, 4), 3), [Fiscal Year Month], ASVP, RSD, Owner Name))