Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:
Not applicable

## What NODISTINCT parameter does in AGGR function?

Hi,

I try to figure out what the NODISTINCT parameter does in the AGGR function. When do we need to use it?

Do you have some clear examaple for this?

Thanks,

Guy

1 Solution

Accepted Solutions
Not applicable
Author

Hi,

I did a little research on this issue, and that's what i found out:

Let assume the bellow data set:

 Country Month Prod Metric IS 1 A1 40 IS 1 A2 50 IR 1 A3 30 IR 2 A1 10

I want to use a AGGR in order to consider the sum by Country and Month only, and let it be presented in a chart with Country, Month and Prod dimensions.

As you can see, the  =AGGR(SUM(Metric),Country,Month) will not fill all the rows data, because the AGGR prepare a data set like this:

 Country Month Metric IS 1 90 IR 1 30 IR 2 10

and when QlikView try to use this set according to the Chart dimensions, it finds out it lacks information about the Prod Dimension, so it fills 90 only in the first row of the combination Country=IS and Month=1. The other row with the same combination gets Null ( - )

 Country Month Prod Simple SUM =AGGR(SUM(Metric),Country,Month) =AGGR(NODISTINCT SUM(Metric),Country,Month) IS 1 A1 40 90 90 IS 1 A2 50 - 90 IR 1 A3 30 30 30 IR 2 A1 10 10 10

We can Solve it by adding NODISTINCT qualifier to the AGGR function, which causes the data to be replicated for the same combination (see above).

I hope it helps!

Guy

6 Replies
MVP

Guy,

I assume you've read the description in the Help, so I am not going to copy it here.

http://community.qlik.com/message/222026

Regards,

Stefan

edit:

http://community.qlik.com/message/221791

Partner - Specialist

Hi:

Sometimes aggr funcktion fills only some rows. If it does not work as expected, add nodistinct and it will.

If you started with nodistinct, and it doesn't work, take it away and see the result.

That's what I do.

The reason is that some rows have more than a single value and it retunrs null.

Not applicable
Author

Hi,

I did a little research on this issue, and that's what i found out:

Let assume the bellow data set:

 Country Month Prod Metric IS 1 A1 40 IS 1 A2 50 IR 1 A3 30 IR 2 A1 10

I want to use a AGGR in order to consider the sum by Country and Month only, and let it be presented in a chart with Country, Month and Prod dimensions.

As you can see, the  =AGGR(SUM(Metric),Country,Month) will not fill all the rows data, because the AGGR prepare a data set like this:

 Country Month Metric IS 1 90 IR 1 30 IR 2 10

and when QlikView try to use this set according to the Chart dimensions, it finds out it lacks information about the Prod Dimension, so it fills 90 only in the first row of the combination Country=IS and Month=1. The other row with the same combination gets Null ( - )

 Country Month Prod Simple SUM =AGGR(SUM(Metric),Country,Month) =AGGR(NODISTINCT SUM(Metric),Country,Month) IS 1 A1 40 90 90 IS 1 A2 50 - 90 IR 1 A3 30 30 30 IR 2 A1 10 10 10

We can Solve it by adding NODISTINCT qualifier to the AGGR function, which causes the data to be replicated for the same combination (see above).

I hope it helps!

Guy

MVP

Very precisely  explained !!

Thanks

Vikas

If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
Contributor II

Hi

You can use Total <Group by Fields> in sum function instead of Aggr too.

like this :

=SUM(Total<Country,Month> Metric)

Have good Day

Saray

Contributor II

Hi,

=AGGR(SUM(Metric),Country,Month) with expression output is different for me.

Community Browser