topic Re: Set analysis not working properly in QlikView

Set analysis not working properly

thymenkristen — Thu, 27 Dec 2018 12:20:08 GMT

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!

Re: Set analysis not working properly

sunny_talwar — Thu, 27 Dec 2018 12:27:23 GMT

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)

Re: Set analysis not working properly

thymenkristen — Thu, 27 Dec 2018 12:41:22 GMT

And how/where can I create such a key?

Re: Set analysis not working properly

OmarBenSalem — Thu, 27 Dec 2018 12:53:06 GMT

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

Re: Set analysis not working properly

thymenkristen — Thu, 27 Dec 2018 13:02:45 GMT

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...

Re: Set analysis not working properly

sunny_talwar — Thu, 27 Dec 2018 13:04:18 GMT

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?

Re: Set analysis not working properly

thymenkristen — Thu, 27 Dec 2018 13:22:38 GMT

I Attached some sample data

Re: Set analysis not working properly

sunny_talwar — Thu, 27 Dec 2018 13:28:59 GMT

Try this

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

Re: Set analysis not working properly

thymenkristen — Thu, 27 Dec 2018 13:39:38 GMT

Great, It works1

I Checked it thoroughly and it returns the correct values!

Thanks alot Sunny!