Expression for Sum(FieldValue) quite similar to Rolling_Balance Calculation

[kakaderanjit53]

Hi Qlik Experts,

Kindly provide me the appropriate Expression solution at Dashboard level of my current requirement,PFB screenshot for more details.

I have to do sum of Transaction Clause By following way:

Dimension:[Transaction Clause]

measure:Amount

I have to calculate the values in column 4th by taking reference of column 3rd;

for E.g. Calculate New FTD:sum(C41:C42)

calculate D/E Aust Mil Bank: sum(C43:C48)

Calculate M/cC Returns: sum(C49:C53)

Thanks ,

Ranjit.

[sunny_talwar]

Without knowing anything about your dashboard (dimensions, expressions), I don't think we will be able to offer much advice... would you be able to share a sample?

[kakaderanjit53]

Hi Sunny Talwar

Sure, I am creating a Pivot table object in qliksense .,PFB screenshot,you will get Idea of the same.

Kindly let me know if you needs more information on the same.

Thanks,

Ranjit.

[kakaderanjit53 · Accepted solution]

Sunny,

I had resolved my query before 2-days ago,Just going to close this discussion.,

I have used following expression for the same:

=============================

Expression:

=============================

if(WildMatch(Only({1} [Transaction Clause]),'11AM CALL')  ,

sum({1<[Transaction Clause]={'11AM CALL'}>}total  $(vNetBalance)),

if(WildMatch(Only({1}[Transaction Clause]),'FIXED TERM')  ,

sum({1<[Transaction Clause]={'FIXED TERM'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'COMMERCIAL SHARES')  ,

sum({1<[Transaction Clause]={'COMMERCIAL SHARES'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'Main Opening Balance')  ,

sum({1<[Transaction Clause]={'Main Opening Balance'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'Main Closing Balance')  ,

sum({1<[Transaction Clause]={'Main Closing Balance'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'Swift Transfers - In') ,

sum({1<[Transaction Clause]={'Swift Transfers - In'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'Swift Transfers - Out') ,

sum({1<[Transaction Clause]={'Swift Transfers - Out'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'Overdraft Interest') ,

sum({1<[Transaction Clause]={'Overdraft Interest'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'Interest Due TPS') ,

sum({1<  [Transaction Clause]={'Interest Due TPS'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'Interest Due 11AM (S10)') ,

sum({1<  [Transaction Clause]={'Interest Due 11AM (S10)'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'Interest Due FTD (I6)

') ,

sum({1<  [Transaction Clause]={'Interest Due FTD (I6)'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'Interest Due Main (S1)') ,

sum({1<  [Transaction Clause]={'Interest Due Main (S1)'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'Term Maturity Funds') ,

sum({1<  [Transaction Clause]={'Term Maturity Funds'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'11AM TRANSFER') ,

sum({1<  [Transaction Clause]={'11AM TRANSFER'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'Cuscal Sec Dep Int') ,

sum({1<  [Transaction Clause]={'Cuscal Sec Dep Int'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'TPS Funds') ,

sum({1<  [Transaction Clause]={'TPS Funds'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'TPS Interest') ,

sum({1<  [Transaction Clause]={'TPS Interest'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'TWT Interest') ,

sum({1<  [Transaction Clause]={'TWT Interest'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'ABACUS') ,

sum({1<  [Transaction Clause]={'ABACUS'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'CUSCAL Dividend') ,

sum({1<  [Transaction Clause]={'CUSCAL Dividend'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'CBS Fee') ,

sum({1<  [Transaction Clause]={'CBS Fee'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'BPAY') ,

sum({1<  [Transaction Clause]={'BPAY'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'Bank Balance Transfer') ,

sum({1<  [Transaction Clause]={'Bank Balance Transfer'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'Sun Daily') ,

sum({1<  [Transaction Clause]={'Sun Daily'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'Sun Pro Monthly') ,

sum({1<  [Transaction Clause]={'Sun Pro Monthly'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'DC FEE INCORRECT REV') ,

sum({1<  [Transaction Clause]={'DC FEE INCORRECT REV'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'SMP JNL') ,

sum({1<  [Transaction Clause]={'SMP JNL'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'O/DRAFT QTLY SERV. FEE') ,

sum({1<  [Transaction Clause]={'O/DRAFT QTLY SERV. FEE'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'INTEGRIS S/FALL PMNT') ,

sum({1<  [Transaction Clause]={'INTEGRIS S/FALL PMNT'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'ATHENA S/FALL PMNT') ,

sum({1<  [Transaction Clause]={'ATHENA S/FALL PMNT'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'merchant fees') ,

sum({1<  [Transaction Clause]={'merchant fees'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'COLLECTIONS SERVICE') ,

sum({1<  [Transaction Clause]={'COLLECTIONS SERVICE'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'CUFSS Levy') ,

sum({1<  [Transaction Clause]={'CUFSS Levy'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'New FTD') ,

sum({1<  [Transaction Clause]={'New FTD'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'D/E Aust Mil Bank 48') ,

sum({1<  [Transaction Clause]={'D/E Credits','D/E Debits','D/E Credit Rtns','D/E Debit Rtns','D/E Aust Mil Bank','D/E Aust Mil Bank 48'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'M/C Returns') ,

sum({1<  [Transaction Clause]={'M/C MEM CHEQ CR','M/C ACC Outward','M/C Bank 80','M/C Outward Transfer','M/C Returns'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'New ATM') ,

sum({1<  [Transaction Clause]={'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'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'Branch Cash') ,

sum({1<  [Transaction Clause]={'Branch Cash'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'LOAN SEC') ,

sum({1<  [Transaction Clause]={'LOAN SEC'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'LOAN FUNDINGS') ,

sum({1<  [Transaction Clause]={'LOAN FUNDINGS'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'VISA IBS RV Fee') ,

sum({1<  [Transaction Clause]={'VISA IBS RV Fee'}>} Total $(vNetBalance)),

if(WildMatch(Only({1} [Transaction Clause]),'VISA') ,

sum({1<  [Transaction Clause]={'VISA'}>} Total $(vNetBalance)),

sum({1<[Transaction Clause]={'*'}>}$(vNetBalance))))))))))))))))))))))))))))))))))))))))))))

Best regards,

Ranjit.