topic Re: Caculation of Total in QlikView

Caculation of Total

Thu, 16 Oct 2014 14:45:32 GMT

Hello everyone, I am encountering a calculation problem:

Here is a simplified table:

item	value	Source
1	90	B
1	100	A
2	50	B
3	60	C

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!

Re: Caculation of Total

maxgro — Thu, 16 Oct 2014 20:05:13 GMT

expression is

sum(aggr(subfield(Concat(value, '@', Source), '@', 1), item))

Re: Caculation of Total

MarcoWedel — Thu, 16 Oct 2014 22:43:20 GMT

Hi,

one solution could be:

hope this helps

regards

Marco

Re: Caculation of Total

jagan — Fri, 17 Oct 2014 03:47:25 GMT

Hi,

Try like this in script

Data:

LOAD

If(Item <> Previous(Item), 1, 0) AS Flag;

LOAD

Item,

Value,

Source

FROM DataSource

ORDER BY Item, Source;

Now use this expression

=Sum({<Flag={1}>} Value)

Hope this helps you.

Regards,

jagan.

Re: Caculation of Total

perumal_41 — Fri, 17 Oct 2014 03:57:48 GMT

Try this

sum({$<Source={"=Sum([value]))<>0"}>}[value])

Re: Caculation of Total

Fri, 17 Oct 2014 10:43:46 GMT

Hi Marco,

It's great! However, I think I simplified too much my model here and I still can't figure out what I should do in my real life application, which is even more tricky:

-If the value is found in A, then we take this value

-If the values are found in B or C, we take the sum of them

item	value	Source
1	90	B
1	100	A
2	50	B
2	40	C
3	60	C

So the result I would expect in my real life application is:

100+(50+40)+60=250

What would you advise?

Thanks a lot!