topic Sum (Amount) for distinct field in QlikView

Sum (Amount) for distinct field

Thu, 13 Mar 2014 09:53:34 GMT

Hi all,

I have the following table

token, amount

A,10

B,20

C,30

and I would calculate total of distinct token, i.e. 10+20+30 = 60 instead of 70

How should I do it?

Thanks!

Re: Sum (Amount) for distinct field

Thu, 13 Mar 2014 09:56:44 GMT

Try...

Sum(DISTINCT amount)

Re: Sum (Amount) for distinct field

Thu, 13 Mar 2014 10:00:01 GMT

yes. Use the Distinct function in your expression.

=Sum(Distinct amount) and token as dimension

Re: Sum (Amount) for distinct field

Thu, 13 Mar 2014 10:02:31 GMT

If you can load distinct values i suggest you to load the distinct values by using distinct keyword in the script. If you can't load distinct values you can use the expression like sum(Aggr(DISTINCT amount,token)) as there might me same amount mapped to different tokens which will give wrong result if you use sum(distinct amount).

Re: Sum (Amount) for distinct field

jsingh71 — Thu, 13 Mar 2014 10:05:10 GMT

find attachment.

Re: Sum (Amount) for distinct field

Thu, 13 Mar 2014 10:05:56 GMT

sum( DISTINCT amount)

Re: Sum (Amount) for distinct field

Thu, 13 Mar 2014 10:06:42 GMT

Hi,

You can do it in expression by using Sum(DISTINCT amount) as expression.

Might be something like attached.

Hope this helps!