Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
i think your expression to be modified like below
COUNT({<[Event Type]={'P'},
[Customer ID] = {"=count([Total Events])>=2"}
>}distinct[Customer ID])
Thanks, @Chanty4u but that's not correct.
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
Hi @marcus_sommer thank you so much, it is correct now.
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
The date condition does not pass to the count again 😞 could you please tell me why it is like this ? @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
What happens if you applies:
sum({< Date={">=$(=addmonths(Max(Date),-12))<=$(=Max(Date))"}>} [Total Events])
in a new table?
- Marcus