Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Exclude from Total

Hi All

I am using the following formula

sum(TOTAL {<Accounts-={'TBD'}>}premium)

/

sum(TOTAL {<Accounts-={'TBD'}>}premium/(1+Rate Chg))

I need the portfolio rate change to calculate an effect.

However, I would like to exclude the TBD accounts, however, the TOTAL overwrites my exclusion "Accounts-={'TBD'}.

I need all dimension of accounts except TBD for the total. How can I make sure the TBD accounts are excluded from the TOTAL?

Many thanks.

Best regards,

Jan

1 Solution

Accepted Solutions
Highlighted

The dimension re-appear, but the total itself doesn't include the value associated with TBD, right? I think you might need to add an if statement here

If(not Match(Accounts, 'TBD'),

sum(TOTAL {<Accounts-={'TBD'}>}premium)

/

sum(TOTAL {<Accounts-={'TBD'}>}premium/(1+Rate Chg))

)

View solution in original post

5 Replies
Highlighted

If you are using this -> Accounts-={'TBD'}, I don't see why Accounts which are TBD are not getting excluded. Have you added Accounts as dimension and see that TBD is getting added?

Highlighted
Contributor III
Contributor III

Yes, for some reason they are added again.

Highlighted

Can you possibly have spaces before or after TBD? Try this

Accounts -= {"*TBD*"}

Highlighted
Contributor III
Contributor III

Unfortunately, that does not change it. And the flag works in other formulae. It is just, if I calculate the total the TBD reappears.

Highlighted

The dimension re-appear, but the total itself doesn't include the value associated with TBD, right? I think you might need to add an if statement here

If(not Match(Accounts, 'TBD'),

sum(TOTAL {<Accounts-={'TBD'}>}premium)

/

sum(TOTAL {<Accounts-={'TBD'}>}premium/(1+Rate Chg))

)

View solution in original post