Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Try this
=Aggr(Count(Aggr(If(Sum(Sales) > 500, Hoofdgroep_Key), Hoofdgroep_Key, Debiteur_Key)), Debiteur_Key)
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)
And how/where can I create such a key?
In the script, in the load part of the table that contains ur 2 fields
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?
I Attached some sample data
Try this
=Aggr(Count(Aggr(If(Sum(Sales) > 500, Hoofdgroep_Key), Hoofdgroep_Key, Debiteur_Key)), Debiteur_Key)
Great, It works1
I Checked it thoroughly and it returns the correct values!
Thanks alot Sunny!