topic Re: distinct of Sum in QlikView

distinct of Sum

Anonymous — Mon, 22 Jan 2018 15:04:49 GMT

I am trying to find out how many distinct SUM of point each Nmae has

I am getting: Error in expression Nested aggregation not allowed when trying : Count(DISTINCT(sum(Point)))

I tried various use of aggr but I have not yet found the solution.

Any idea?

for example for:

I would expect to see:

Re: distinct of Sum

vishsaggi — Mon, 22 Jan 2018 15:12:41 GMT

You might want to use like

= Count(Aggr(Sum(DISTINCT Point), Name))

Re: distinct of Sum

Anil_Babu_Samineni — Mon, 22 Jan 2018 15:14:17 GMT

May be this?

Count(Aggr(Sum(Point),Name))

Re: distinct of Sum

Anonymous — Mon, 22 Jan 2018 15:14:24 GMT

does it work for you?

for me it does not, it gives 2 for A and 1 for B, should give 1 for both A and B

Thanks

Re: distinct of Sum

Anonymous — Mon, 22 Jan 2018 15:19:09 GMT

no, it gives 1 and 1 in this case but also when not true

Re: distinct of Sum

vishsaggi — Mon, 22 Jan 2018 15:21:33 GMT

Sorry did not check your output. I have updated the expression. LIke

= Count(Aggr(Sum(DISTINCT Point), Name))

Re: distinct of Sum

Anonymous — Mon, 22 Jan 2018 15:36:05 GMT

no for example for

LOAD * INLINE [

Name, Point, Date

A, 2, 01/01/2018

A, 400000, 01/01/2018

A, 2, 02/01/2018

A, 1, 02/01/2018

A, 2, 05/01/2018

A, 4, 05/01/2018

B, 6, 01/01/2018

B, 6, 02/01/2018

B, 7, 05/01/2018

B, 600000, 05/01/2018

];

also gives 1 and 1 ?

Re: distinct of Sum

vishsaggi — Mon, 22 Jan 2018 15:53:45 GMT

What is your expected output for this data?

Re: distinct of Sum

Anonymous — Mon, 22 Jan 2018 15:58:01 GMT

A = 3 as over the period there were 2 distinct Sum 400002 ,3, 6

B = 2 as over the period there were 2 distinct Sum 12 and 600007

Re: distinct of Sum

Anil_Babu_Samineni — Mon, 22 Jan 2018 16:13:34 GMT

Can you show image after use this?

Count(DISTINCT Date)

Re: distinct of Sum

Anonymous — Mon, 22 Jan 2018 16:16:02 GMT

3 and 3

Re: distinct of Sum

Anil_Babu_Samineni — Mon, 22 Jan 2018 16:17:50 GMT

If you look data, You will understand the Result

Re: distinct of Sum

Anonymous — Mon, 22 Jan 2018 16:22:13 GMT

No ,

Should be

A = 3 as over the period there were 2 distinct Sum 400002 ,3, 6

B = 2 as over the period there were 2 distinct Sum 12 and 600007

Re: distinct of Sum

Anil_Babu_Samineni — Mon, 22 Jan 2018 16:26:08 GMT

May be this?

Count(Aggr(Sum(TOTAL <Date> Point), Date, Name))

Re: distinct of Sum

Anonymous — Mon, 22 Jan 2018 16:27:10 GMT

Thank you!