topic Re: Calculate Sum for distinct dimensions in QlikView

Calculate Sum for distinct dimensions

Wed, 18 Jul 2012 19:01:09 GMT

Hi,

I have a table with

Name1 Name2 Value

1 2 1

1 3 1

1 4 1

2 5 2

2 6 2

I want to calculate sum of amount where Name 1 is distinct

Currently, when I do a sum(Value) it gives me 1+1+1=3 and 2+2=4

Instead, I want it for Name1=1 the sum shld be 1 and name 2 = 2

and then total shld be 1+2 = 3

Please help

Wed, 18 Jul 2012 19:09:28 GMT

sum(aggr(sum(DISTINCT Value), Name1))

nagaiank — Wed, 18 Jul 2012 19:14:02 GMT

The solution given by happyjihad will work.

If you want to do it in script, use the script

Data:

LOAD * Inline [

Name1,Name2,Value

1,2,1

1,3,1

1,4,1

2,5,2

2,6,2

];

Data2:

LOAD Name1, Sum(DISTINCT Value) as Val Resident Data Group By Name1;

If you want to use in a chart,

Dimension: Name1

Expression: aggr(sum(DISTINCT Value), Name1)

Wed, 03 Oct 2012 14:36:35 GMT

I know this wasn't my question, but your answer was incredibly helpful to me. Thank you!