topic Re: Exclude from Total in QlikView

Exclude from Total

schumi1980 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Exclude from Total

sunny_talwar — Wed, 24 Oct 2018 16:18:33 GMT

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?

Re: Exclude from Total

schumi1980 — Wed, 24 Oct 2018 16:24:12 GMT

Yes, for some reason they are added again.

Re: Exclude from Total

sunny_talwar — Wed, 24 Oct 2018 16:31:44 GMT

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

Accounts -= {"*TBD*"}

Re: Exclude from Total

schumi1980 — Wed, 24 Oct 2018 17:05:48 GMT

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

Re: Exclude from Total

sunny_talwar — Wed, 24 Oct 2018 17:08:25 GMT

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

)