Get date where count distinct is lowest

[amaaiia]

Hi!

I have the following example of data:

date1,type,field1

2023-01-01,'aaa','v1'

2023-01-01,'aaa','v2'

2023-01-01,'bbb','v3'

2023-01-03,'aaa','v1'

2023-01-03,'bbb','v5'

2023-01-03,'bbb','v1'

I have a kpi where I show the date1 with de minimum measure1 (count(distinct field1)) and the date1 that gets this minimum measure1. If one date1 doesn't have data (in this case, 2023-01-02 has not data), this date1 has to be the one with the minimum measure1.

I have loaded a calendar with the list of the possible date1, in this case from 2023-01-01 to 2023-01-03 (including 2023-01-02), just Qlik to know all dates have to be analysed.

So,

date1, measure1

2023-01-01,3

2023-01-02,0 --> min

2023-01-03,2

I have measure1 as follows (date1 (min(field1))):

FirstSortedValue({State1*State2}distinct date1, Aggr(if(len(concat(field1))>0,count({State1*State2}distinct field1),0),date1)))

& ' (' &
Min({State1*State2}aggr(if(len(concat(field1))>0,count({State1*State2}distinct field1),0), date1))

& ')'

This works, but the problem is that when I filter type dimension values, measure1 breaks. If  I select 'aaa' value of type dimension, 2023-01-02 is still the date1 with the lowest amount. I guess the problem is I'm not including type inside aggr function, but If I do so It doesn't work.

[Chanty4u]

Hi try this .

FirstSortedValue({State1*State2} distinct date1,

    Aggr(if(len(concat(field1)) > 0, count({State1*State2} distinct field1), 0), date1, type)

) & ' (' &

Min({State1*State2} aggr(if(len(concat(field1)) > 0, count({State1*State2} distinct field1), 0), date1, type))

&

')'

[Benoit_C]

Hi @amaaiia,

If you modify your expression to include the type dimension in the Aggr function, like below is it helping ?

FirstSortedValue({State1*State2}distinct date1, Aggr(if(len(concat(field1))>0,count({State1*State2}distinct field1),0),date1,type))
& ' (' &
Min({State1*State2}aggr(if(len(concat(field1))>0,count({State1*State2}distinct field1),0), date1,type))
& ')'

Regards,

Benoit
 

[amaaiia]

No, it doesn't work, I've already tried it. Also the problem is that I have some other dimension that I can filter, not only type

[Benoit_C · Accepted solution]

Hi @amaaiia,

You could try using a set analysis expression to ignore the selections in those dimensions. For example:
 

FirstSortedValue({State1*State2}distinct date1, Aggr(if(len(concat(field1))>0,count({State1*State2<type=>}distinct field1),0),date1))
& ' (' &
Min({State1*State2}aggr(if(len(concat(field1))>0,count({State1*State2<type=>}distinct field1),0), date1))
& ')'

Does that work ?

Regards,
Benoit