Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
q11hhg
Contributor III
Contributor III

Count set analysis does not return expected results

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

q11hhg_1-1614764152833.png

 

 

Labels (2)
16 Replies
q11hhg
Contributor III
Contributor III
Author

returns me a correct result 

q11hhg_0-1614775473039.png

 

q11hhg
Contributor III
Contributor III
Author

I tried to fix it by changing the expression to a date range like this

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

I will get an error .

q11hhg_1-1614779674378.png

 

 

marcus_sommer
MVP & Luminary
MVP & Luminary

That this expression-part here works showed that the applied date-logic itself and the syntax is generally correct and should be therefore also work within the whole expression. It's difficult to guess why not.

To exclude that yet any formatting-stuff caused it try the following variants (not all together - unless properly commented):

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

- Marcus

q11hhg
Contributor III
Contributor III
Author

Hi @marcus_sommer  I very much appreciate your help,  I have tried all of them but they all end up with no results. 

q11hhg
Contributor III
Contributor III
Author

And my data is very simple, the date column is formate correctly as date

marcus_sommer
MVP & Luminary
MVP & Luminary

If your other expression-steps from above work it should also work combined. At the moment I don't see an obvious issue. In similar cases I would reduce all possible complexity and looking if it returned the expected values and then adding step by step more conditions/variables/expression-parts to find the point where it breaks respectively it returned unexpected results. In your case you may start with something like this:

COUNT({< Date={">=43886"}>} distinct [Customer ID])
COUNT({< Date={">=43886<=43888"}>} distinct [Customer ID])

- Marcus

manoranjan_d
Specialist
Specialist

Hi ,

First try to create a flag in script level for sum of total event is >=2 as below 

if sum([Total Events])>=2,'Y',"N') as flag-->script level add this code and also 

and then in the expression try this

COUNT({<[Event Type]={'P'},flag={'Y'},

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

>} distinct[Customer ID])