1 Reply Latest reply: Sep 27, 2016 9:37 PM by Sunny Talwar RSS

    Set Analysis in the Pivot table

      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)