topic Re: Caculation of Total in QlikView

Caculation of Total

Thu, 16 Oct 2014 14:23:31 GMT

Hello everyone, I am encountering a calculation problem:

Here is a simplified table:

The calculation rule is as follows:

for an Item that can be found in the source A(<>0), we should take the value from source A, otherwise we take the value from Source B.

If we find values from both source A(<>0) and B(<>0), we still take the value from source A.

The same logic applies where there are more sources.

How can we get the right total by using a formula? In the above example, the total should be 100+50+60=210?

Here's the formula I used but I don't get the right answer.

=if(sum({$<Source={'A'}>}[value])<>0,

sum({$<Source{'A'}>}[value]),

if(sum({$<Source={'B'}>}[value])<>0,

sum({$<Source={'B'}>}[value]),

if(sum({$<Source={'C'}>}[value])<>0,

sum({$<Source={'C'}>}[value])

)))

Thanks for your help!

maternmi — Thu, 16 Oct 2014 14:30:56 GMT

Hi,

you neet to add "total", try this.

=sum(total (if(sum({$<Source={'A'}>}[value])<>0,

sum({$<Source{'A'}>}[value]),

if(sum({$<Source={'B'}>}[value])<>0,

sum({$<Source={'B'}>}[value]),

if(sum({$<Source={'C'}>}[value])<>0,

sum({$<Source={'C'}>}[value])

)))))

BR Michael

Thu, 16 Oct 2014 14:39:02 GMT

Hello Michael,

Thanks for your reply, however I get an error message: Nested aggregation not allowed. Can you advise?

Regards,