Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kakaderanjit53
Creator III
Creator III

Result of expression is not displaying in pivot table.

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 .

0 Replies