Count set analysis does not return expected results

[q11hhg]

Hi there , I am struggling to understand why my expression gives a wrong result.

I want to calculate customers with event type P whose total events is >= 2. The answer should be 3, but my set analysis giving me 4. 

COUNT({<[Event Type]={'P'},
[Customer ID] = {"=sum([Total Events])>=2"}
>}distinct[Customer ID]

The expression seems to ignore the Event Type ='P'.

Could you please help me? I am very new to qliksense.

Thank you

[Chanty4u]

i think your expression to be modified like below

COUNT({<[Event Type]={'P'},
[Customer ID] = {"=count([Total Events])>=2"}
>}distinct[Customer ID])

[q11hhg]

Thanks, @Chanty4u  but that's not correct. 

[marcus_sommer · Accepted solution]

Your [Event Type] condition isn't applied to the sum() and therefore there not regarded. You may change your expression like:

COUNT({<[Customer ID] = {"=sum({< [Event Type]={'P'}>} [Total Events])>=2"}
>} distinct[Customer ID]

- Marcus

[q11hhg]

Hi @marcus_sommer  thank you so much, it is correct now.

[q11hhg]

Hi @marcus_sommer , I have another question, for example I want to include  the last 12 month condition to the expression. Like this:

COUNT({<
[Customer ID] = {"=sum({< [Event Type]={'P'}>} [Total Events])>=2"

,Date={">=$(=date(addmonths(Max(Date),-12),'YYYY-MM-DD'))<=$(=date(Max(Date),'YYYY-MM-DD'))"}

>} distinct[Customer ID])

is it still correct? 

Thank you 

[q11hhg]

The date condition does not pass to the count again 😞 could you please tell me why it is like this ? @marcus_sommer 

[marcus_sommer]

It's the same like above - the condition must be applied to the sum() or in other words to the inner condition and not to the outer condition, maybe like this:

COUNT({<
[Customer ID] = {"=sum({< [Event Type]={'P'},
                                                      Date={">=$(=addmonths(Max(Date),-12))<=$(=Max(Date))"}>}
                                      [Total Events])>=2"}>}
distinct [Customer ID])

As far as the comparison is set to >= <= a formatting won't be needed because the evaluation happens on a numeric level - therefore date() was removed.

- Marcus

[q11hhg]

Hi @marcus_sommer  the express does not return me any results. 

[marcus_sommer]

What happens if you applies:

sum({< Date={">=$(=addmonths(Max(Date),-12))<=$(=Max(Date))"}>} [Total Events])

in a new table?

- Marcus