Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to get value, depending on unique value of other fields

Hello all!

I have a problem which I have simplified below. I have the following fields in a data table:

SALES

ENTRY_ID

Due to the necesity to built a star sheme as data model, I needed to join this table with some others resulting to a final table which looks like this:

SALES

ENTRY_ID

EXTRA_FIELD

I need to sum the SALES but only for thoose records where the entry_ID is distinct. In my first data model I would have done:

sum(SALES)

Now I will do:

sum(aggr(only(SALES),ENTRY_ID))

(is that right?)

Now the problem is, I need to use this function in a chart with as dimension KPI_ID (a fourth value, independent on the first three values). To avoid the problem stated here (http://community.qlik.com/message/166081#166081), I will need to add 'nodistinct' to my expression:

sum(aggr(nodistinct only(SALES),ENTRY_ID))

However, now the result gives me the same as if I would use sum(SALES), all the SALES are included, even thoose that have the same ENTRY_ID!! How can I solve this problem?

Please also see the attached qvw.

Thank you very much!

1 Solution

Accepted Solutions
Not applicable
Author

Ok I found the anser to the problem!

Apparentely in order to avoid the use of NODISTINCT in the AGGR() function, you can involve the problematic dimension (in my case the KPI_ID) in the aggregation dimensions. So the correct expression to use is:

sum(aggr(nodistinct only(SALES),ENTRY_ID,KPI_ID))

simplifying a problem is the way to go when you've spent hours on puzzling out something complex

Thank you Celambarasan Adhimulam for your help!

View solution in original post

5 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Try with this

     sum(aggr(if(Count(ENTRY_ID)=1,Sum(SALES)),ENTRY_ID))

Celambarasan

Not applicable
Author

That just gives me a bunch of zeros 😕

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     You mean for all entry ID it gives zeros or for duplicate ID's?

Celambarasan

Not applicable
Author

In a chart with EXTRA_FIELD as dimension, the given expression results in 0's for every record in EXTRA_FIELD.

But I realize that I wasn't entirely correct in my simplified situation I described in my first post. Creating a chart with EXTRA_FIELD as dimension and this as expression:

sum(aggr(nodistinct only(SALES),ENTRY_ID))

works fine. The problem occurs when I have a fourth variable not linked to the previous three variables, lets call it KPI_ID. Making a chart with this field as dimension and as expression:

sum(aggr(nodistinct only(SALES),ENTRY_ID))

will result in incorrect somation as the complete SALES, even with nodistinct ENTRY_IDs, are taken into account. using:

sum(aggr(only(SALES),ENTRY_ID))

will result in only 1 calculated value...

I will update my first post in order to reflect the correct situation.

Not applicable
Author

Ok I found the anser to the problem!

Apparentely in order to avoid the use of NODISTINCT in the AGGR() function, you can involve the problematic dimension (in my case the KPI_ID) in the aggregation dimensions. So the correct expression to use is:

sum(aggr(nodistinct only(SALES),ENTRY_ID,KPI_ID))

simplifying a problem is the way to go when you've spent hours on puzzling out something complex

Thank you Celambarasan Adhimulam for your help!