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: 
schumi1980
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
sunny_talwar

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
sunny_talwar

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?

schumi1980
Contributor III
Contributor III
Author

Yes, for some reason they are added again.

sunny_talwar

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

Accounts -= {"*TBD*"}

schumi1980
Contributor III
Contributor III
Author

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

sunny_talwar

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))

)