Set analysis not working properly

[thymenkristen]

Hi all,

My goal is to count the distinct number of Hoofdgroep_key(s), if the sum of Sales > 500, per Debiteur_Key

Currently i have the following calculated dimension.

=aggr(Count(DISTINCT{<Hoofdgroep_Key={"=sum(Sales)> 500"}>} Hoofdgroep_Key), Debiteur_Key)

But it is not working properly.

Becuase, without any selections the count is returning 9.

And after the selection of a single Debiteur_Key the count is returning 2. Which is the correct value.

 All help is welcome, thanks in advance!

[sunny_talwar]

May be you need to create a key which combines Hoofdgroep_Key and Debiteur_Key into a single field to use within your set analysis.

Debiteur_Key&'|'&Hoofgroep_Key as New_Key

and then this

=Aggr(Count(DISTINCT{<New_Key = {"=Sum(Sales) > 500"}>} Hoofdgroep_Key), Debiteur_Key)

[thymenkristen]

And how/where can I create such a key?

[OmarBenSalem]

In the script, in the load part of the table that contains ur 2 fields

[thymenkristen]

Is this the only option to solve my problem?
Since my Qlikview file uses a BINARY statement to loading this specific data from another document...

[sunny_talwar]

The other option might need to use another aggr() function combined with if statement.... but it would be difficult to give the exact expression without looking at the data. Would you be able to provide a sample?

[thymenkristen]

I Attached some sample data

[sunny_talwar · Accepted solution]

Try this

=Aggr(Count(Aggr(If(Sum(Sales) > 500, Hoofdgroep_Key), Hoofdgroep_Key, Debiteur_Key)), Debiteur_Key)

[thymenkristen]

Great, It works1

I Checked it thoroughly and it returns the correct values!

Thanks alot Sunny!