Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
q11hhg
Contributor III
Contributor III

Set analysis with 3 conditions

Hi there, 

I want to calculate the number of customers with an event name = 'P', the sum of total events >=2, and within the last 12 months.

I am using the following expression, but it does not return any value.

 

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

Could you please help.

Labels (2)
9 Replies
Chanty4u
MVP
MVP

use month and try

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

manoranjan_d
Specialist
Specialist

First try to create flag in the script for sum(total events) >=2 and then call the flag in the set analysis

 

I hope already replied to your pervious thread 

q11hhg
Contributor III
Contributor III
Author

My script like this, If I create a flag as you suggested. I need to use group by and group by date, then that would not work.

LOAD
"Event Type",
"Customer ID",
"Date",
"Total Events"
FROM [lib://AttachedFiles/94cc618a-ac95-49f8-a660-3e2633e6e067.xlsx]
(ooxml, embedded labels, table is Sheet1);

q11hhg
Contributor III
Contributor III
Author

besides I also need to use the total events column for other calculation.

manoranjan_d
Specialist
Specialist

From your above fact table drive your dimension table and link this table. in the dimension add your required fields as grp by 

q11hhg
Contributor III
Contributor III
Author

Hi @manoranjan_d , could you please describe in more detail? I am very new to Qlik sense, Thank you 

manoranjan_d
Specialist
Specialist

do you want to calculate the [Customer ID] who is having sum   [Total Events])>=2? if so 

then you link your fact table key -customer id with dimension table.

example:

load 

if(sum(total event >=2,'Y','N')

resident Table

group by customer ID

q11hhg
Contributor III
Contributor III
Author

Yes but it would not work, I need to not only calculate that but also take into consideration the last 12 months.

Adding that script only gives me the customer who >=2 but in my expression, I also need to add a date filter.

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

I tried your suggestion by adding

test:
load

if(sum("Total Events" >=2),'Y','N') AS Flag,
"Customer ID",

Resident Events
Where [Event Type]='Login'
group by "Customer ID",

and then linked with my fact table :

COUNT({<
Date={">=$(=date(addmonths(Max(Date),-12),'YYYY-MM-DD'))<=$(=date(Max(Date),'YYYY-MM-DD'))"},
Flag={'Y'}
>} distinct[Customer ID])

 

it does not return correct result 

manoranjan_d
Specialist
Specialist

pass 11 instead of 12

 

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