0 Replies Latest reply: Feb 18, 2018 2:48 PM by RANJIT KAKADE RSS

    Result of expression is not displaying in pivot table.

    RANJIT KAKADE

      Hi Qlik Experts,

       

      I have to display the following details by using given expression in pivot table., I am using following dimension expression in the row:

       

      =ValueList('Swift Transfer In',

      'Swift Transfer Out',

      'Overdraft Interest',

      'Interest Due TPS',

      'Interest Due 11AM (S10)',

      'Interest Due FTD (I6)',

      'Interest Due Main (S1)',

      'Term Maturity Funds',

      '11AM TRANSFER',

      ' ',

      ' ',

      'Cuscal Sec Dep Int',

      'TPS Funds',

      'TPS Interest',

      'TWT Interest',

      'ABACUS',

      'CUSCAL Dividend',

      'CBS Fee',

      'BPAY',

      'Bank Balance Transfer',

      'Sun Daily',

      'Sun Pro Monthly',

      'DC FEE INCORRECT REV',

      'SMP JNL',

      'O/DRAFT QTLY SERV. FEE',

      'INTEGRIS S/FALL PMNT',

      'ATHENA S/FALL PMNT',

      'merchant fees',

      'COLLECTIONS SERVICE',

      'CUFSS Levy',

      'New FTD',

      ' ',

      'D/E Credits',

      'D/E Debits',

      'D/E Credit Rtns',

      'D/E Debit Rtns',

      'D/E Aust Mil Bank Dr',

      'D/E Aust Mil Bank Cr',

      'M/C MEM CHEQ CR',

      'M/C ACC Transfer',

      'M/C Bank 80',

      'M/C Outward Transfer',

      'M/C Returns',

      'ATM Issuer DC - WDL + ENQ',

      'ATM EFT REF#',

      'ATM SETTLEMENT REDICARD',

      'ATM BRANCH CASH ADJ',

      'ATM FEES',

      'REDIATM SETTLEMENT',

      'ATM DARWIN',

      'ATM Stirling',

      'ATM Robertson',

      'ATM HOLSWORTHY',

      'ATM Edinburgh',

      'Kapooka ATM',

      'Enogerra ATM',

      'Navy Canteen',

      'Larrakeyah ATM',

      'Cerberus ATM',

      'Puckapunyal ATM',

      'Watson ATM',

      'Albatross ATM',

      'Lavarack ATM',

      'Laverton ATM',

      'ATH SUBSERVICER FEE',

      'ATH DISTRIBUTION',

      'New ATM',

      'Branch Cash',

      'LOAN SEC',

      'LOAN FUNDINGS',

      'VISA IBS RV Fee',

      'VISA')

       

      and in the measure using following expression., but even correct expression not showing required data in the Pivot table:

      PFB details  and help me to solve the issue.

       

      measure expression:

       

      Pick(Match(

       

       

      ValueList('Swift Transfer In',

      'Swift Transfer Out',

      'Overdraft Interest',

      'Interest Due TPS',

      'Interest Due 11AM (S10)',

      'Interest Due FTD (I6)',

      'Interest Due Main (S1)',

      'Term Maturity Funds',

      '11AM TRANSFER',

      ' ',

      ' ',

      'Cuscal Sec Dep Int',

      'TPS Funds',

      'TPS Interest',

      'TWT Interest',

      'ABACUS',

      'CUSCAL Dividend',

      'CBS Fee',

      'BPAY',

      'Bank Balance Transfer',

      'Sun Daily',

      'Sun Pro Monthly',

      'DC FEE INCORRECT REV',

      'SMP JNL',

      'O/DRAFT QTLY SERV. FEE',

      'INTEGRIS S/FALL PMNT',

      'ATHENA S/FALL PMNT',

      'merchant fees',

      'COLLECTIONS SERVICE',

      'CUFSS Levy',

      'New FTD',

      ' ',

      'D/E Credits',

      'D/E Debits',

      'D/E Credit Rtns',

      'D/E Debit Rtns',

      'D/E Aust Mil Bank Dr',

      'D/E Aust Mil Bank Cr',

      'M/C MEM CHEQ CR',

      'M/C ACC Transfer',

      'M/C Bank 80',

      'M/C Outward Transfer',

      'M/C Returns',

      'ATM Issuer DC - WDL + ENQ',

      'ATM EFT REF#',

      'ATM SETTLEMENT REDICARD',

      'ATM BRANCH CASH ADJ',

      'ATM FEES',

      'REDIATM SETTLEMENT',

      'ATM DARWIN',

      'ATM Stirling',

      'ATM Robertson',

      'ATM HOLSWORTHY',

      'ATM Edinburgh',

      'Kapooka ATM',

      'Enogerra ATM',

      'Navy Canteen',

      'Larrakeyah ATM',

      'Cerberus ATM',

      'Puckapunyal ATM',

      'Watson ATM',

      'Albatross ATM',

      'Lavarack ATM',

      'Laverton ATM',

      'ATH SUBSERVICER FEE',

      'ATH DISTRIBUTION',

      'New ATM',

      'Branch Cash',

      'LOAN SEC',

      'LOAN FUNDINGS',

      'VISA IBS RV Fee',

      'VISA'),

       

       

      Sum({1<FLAG ={'POSITIVE'},Column_D={"*IPFX*"}>}Column_E),

       

       

      Sum({1<FLAG ={'NEGATIVE'},Column_D={"*IPFX*"}>}Column_E),

       

       

      Sum({1<Column_D={ "*O/D INT*"}>}Column_E),

       

       

      0.00,

       

       

      Sum({1<Customer_Name={"*INTEREST*"},Column_D = {'11AM'}>}Column_E),

      Sum({1<Customer_Name={"*INTEREST*"},Column_D = {"*SI Int*"}>}Column_E),

       

       

      Sum({1<Customer_Name={"*INTEREST CR*"},Column_D = {"*MAIN ACCOUNT*"}>}Column_E),

      0.00,

      Sum({1<Customer_Name={"*S10 - 11AM CALL*"},Column_D = {"*MAIN ACCOUNT*"}>}Column_E)*-1,

      0.00,

      0.00,

      sum({1<Column_D={"*TRANSFER FROM 642170I6*"}-{'D/E'}>}Column_E),

       

       

       

       

      Sum({1<Column_D={"*TPS*"}-{'D/E'}>}Column_E),

      Sum({1<Column_D={"*TPS INTEREST*"}>}Column_E),

      Sum({1<Column_D={"*TWT*"}>}Column_E),

      0.00,

      Sum({1<Column_D={"*FINAL DIVIDEND*","*INTERIM DIVIDEND*"}>}Column_E),

      Sum({1<Column_D={"CBS A/C FEE"}>}Column_E),

      Sum({1< Column_D = {"*BPAY*"}>}Column_E),

      Sum({1< Column_D = {"*bank bal trf*"}>}Column_E),

      Sum({1< Column_D = {"*SUN Daily*"}>}Column_E),

      Sum({1< Column_D = {"*SUN PRO MON*"}>}Column_E),

      Sum({1< Column_D = {"*FEE INCORRECT RE*"}>}Column_E),

      Sum({1< Column_D = {"*SMP JNL*"}>}Column_E),

      Sum({1< Column_D = {"*ODRAFT QTLY SERV*"}>}Column_E),

      Sum({1< Column_D = {"*INTEGRIS*"}-{'D/E'}>}Column_E),

      Sum({1< Column_D = {"*ATHENA*"}-{'D/E'}>}Column_E),

      Sum({1< Column_D = {"*merchant*"}-{'D/E'}>}Column_E),

      Sum({1< Column_D = {"*CUCS TRF*"}-{'D/E'}>}Column_E),

      Sum({1< Column_D = {"*CUFSS*"}-{'D/E'}>}Column_E),

      Sum({1< Column_D = {"*I6 - FIXED TERM*"}>}Column_E)*-1,

      ' ',

      //if(Sum({1< Column_D = {"*D/E*"}-{'D/E RTN'}>}Column_E)>0,Sum({1< Column_D = {"*D/E*"}-{'D/E RTN'}>}Column_E)),

      Sum({1< Column_D = {"*D/E*"}-{'D/E RTN'},Column_E={">0"}>}Column_E)-Sum({1<Column_D={"*D/E Australian m*"},FLAG={'NEGATIVE'}>}Column_E),

      Sum({1< Column_D = {"*D/E*"}-{'D/E RTN'},Column_E={"<0"}>}Column_E)-Sum({1<Column_D={"*D/E Australian m*"},FLAG={'POSITIVE'}>}Column_E),

       

       

      Sum({1< Column_D = {"*D/E RTN*"},FLAG={'POSITIVE'}>}Column_E),

      Sum({1< Column_D = {"*D/E RTN*"},FLAG={'NEGATIVE'}>}Column_E),

      Sum({1<Column_D={"*D/E Australian m*"},FLAG={'POSITIVE'}>}Column_E),

      Sum({1<Column_D={"*D/E Australian m*"},FLAG={'NEGATIVE'}>}Column_E),

      //If(Sum({1<Column_D={"*D/E Australian m*"}>}Column_E)<0,Sum({1<Column_D={"*D/E Australian m*"}>}Column_E)),

      Sum({1<Column_D={"*D/E Australian m*","*M/C ACC Outward*"}>}Column_E),

      Sum({1<Column_D={"*M/C ACC Inward*"}>}Column_E),

      Sum({1<Column_D={"*M/C BANK 80*"}>}Column_E),

      Sum({1<Column_D={"*M/C REV*"}>}Column_E),

      Sum({1<Column_D={"*M/C CHQ'S PAID*"}>}Column_E),

      //Sum({1<p(Column_D={"*ATM*"})*p(Column_D={"*ISSUER dc*"})>}Column_E),

      Sum({1<Column_D = {"*ATM*"},Column_D = {"*ISSUER dc*"}>}Column_E),

      Sum({1<Column_D={"*ATM EFT*"}>}Column_E),

      Sum({1<Column_D={"*rediCARD Settlement*"}>}Column_E),

      Sum({1<Column_D={"*ATM BRCH AD*"}>}Column_E),

      Sum({1<Column_D={"*ATM*"},Column_D={"*Acquirer DC*"}>}Column_E),

      Sum({1<Column_D={"*rediATM SETTLEMENT*"}>}Column_E),

      Sum({1<Column_D={"*64215833*"}>}Column_E),

      Sum({1<Column_D={"*21706896*"}>}Column_E),

      Sum({1<Column_D={"*21705870*"}>}Column_E),

      Sum({1<Column_D={"*20552275*"}>}Column_E),

      Sum({1<Column_D={"*20552275*"}>}Column_E),

      Sum({1<Column_D={"*20555282*"}>}Column_E),

      Sum({1<Column_D={"*20554281*"}>}Column_E),

      Sum({1<Column_D={"*20557855*"}>}Column_E),

      Sum({1<Column_D={"*64215860*"}>}Column_E),

      Sum({1<Column_D={"*20553864*"}>}Column_E),

      Sum({1<Column_D={"*21703957*"}>}Column_E),

      Sum({1<Column_D={"*21702937*"}>}Column_E),

      Sum({1<Column_D={"*21702938*"}>}Column_E),

      Sum({1<Column_D={"*20554862*"}>}Column_E),

      Sum({1<Column_D={"*20553863*"}>}Column_E),

      Sum({1<Column_D={"*SUBSERVICER FEE*"}>}Column_E),

      Sum({1<Column_D={"*ATH DISTRIBUTION*"}>}Column_E),

      Sum({1<Column_D={"*64215861*"}>}Column_E),

      Sum({1<Column_D={"*Branch Col*"}>}Column_E)+Sum({1<Column_D={"*BRANCH DEL*"}>}Column_E),

      Sum({1<Column_D={"*LoanSec*"}>+<Column_D={"*Loan Sec*"}>}Column_E),

       

       

      //if((Sum({1<Column_D={"*LoanSec*","*Loan Sec*"}>}Column_E)*-1)>0,Sum({1<Column_D={"*LoanSec*","*Loan Sec*"}>}Column_E)*-1),

      IF(SUM({1<Column_D={"*LoanSec*"}>+<Column_D={"*Loan Sec*"}>}Column_E)*-1>=200000,SUM({1<Column_D={"*LoanSec*"}>+<Column_D={"*Loan Sec*"}>}Column_E)*-1,0),

      Sum({1<Customer_Name={"*S1*"},Column_D={"*IBS*"}>}Column_E))

      )

       

       

      Capture.PNG

       

      Thanks,

      Ranjit .