Sum( IF() ) not working

[ElsKnockaert]

Good afternoon! 

I have 1913 customers that have not been visited in a certain period (last 2 months).

I have 435 customers that have orders of the order type 001 in that period (last 2 months).

I want to see the intersection of those 2 columns in my 3rd column. So I tried with an IF statement, but my sum does not work.

My goal: 

* That the date period works with a selection of Cal_Year and Cal_Month and Cal_Day and does not have to be hard coded in the set expression as it is now

* That the sum works in column 3, either with an IF statement as it is now, of if somebody knows how to pour this intersection into a set expression, that would be nice!

Thank you for any help!

column 1 : 

count(distinct Cust_Internal_Id) - count({<Visit_Id={*},Cal_Date={ ">=$(=addmonths(max(Cal_Date),-2))<=$(=max(Cal_Date))"}>}Cust_Internal_Id)

column 2: 

count( {<Order_Type_Id={'001'},Cal_Date={ ">=$(=addmonths(max(Cal_Date),-2))<=$(=max(Cal_Date))"} >} distinct Cust_Internal_Id)

column 3:

sum(aggr(
If( column(1) + count( {<Order_Type_Id={'001'},Cal_Date={ ">=$(=addmonths(max(Cal_Date),-2))<=$(=max(Cal_Date))"} >} distinct Cust_Internal_Id)
> 1
,1
,0
)
,Cust_Internal_Id, Cust_Id, Cust_Name, Cust_Territory))

[Oleg_Troyansky]

Hi there!

I can suggest a direction to the Set Analysis solution for this problem. In your third column, you are looking for Customers that satisfy two conditions - having orders of a certain type and not being called. Hence, your Set Analysis condition should include an intersection of these two. I'd construct a Set Analysis filter, based on the Customer field, along these lines:

Cust_Internal_Id =

P({<Cust_Internal_ID = {"=count(distinct Cust_Internal_Id) - count({<Visit_Id={*},Cal_Date={ ">=$(=addmonths(max(Cal_Date),-2))<=$(=max(Cal_Date))"}>}Cust_Internal_Id) > 0"} >}    ) * 

P({<Cust_Internal_ID = {"=count( {<Order_Type_Id={'001'},Cal_Date={ ">=$(=addmonths(max(Cal_Date),-2))<=$(=max(Cal_Date))"} >} distinct Cust_Internal_Id) > 0"} >}    )

You are getting the intersection of the possible values in the field Customer ID, based on your two conditions that are defined in two "expression searches". Perhaps there is a way to combine the two conditions in one search, but this way appears more intuitive and repeatable to me.

To learn more about advanced uses of Set Analysis, come to the next Masters Summit for Qlik, where I teach Set Analysis, AGGR, and other advanced development methodologies.

Cheers, 

[ElsKnockaert]

Thank you so much! This would be my preferred solution, but when I copy and paste, it doesn't seem to work. 

[SRA · Accepted solution]

Hi,

This one should also work :

count({ (1-<Visit_Id={"*"},Cal_Date={ ">=$(=addmonths(max(Cal_Date),-2))<=$(=max(Cal_Date))"}>) * <Order_Type_Id={'001'},Cal_Date={ ">=$(=addmonths(max(Cal_Date),-2))<=$(=max(Cal_Date))"} > } distinct Cust_Internal_Id)

I replaced :

count(distinct Cust_Internal_Id) - count({<Visit_Id={*},Cal_Date={ ">=$(=addmonths(max(Cal_Date),-2))<=$(=max(Cal_Date))"}>}Cust_Internal_Id)

By :

count({ 1-<Visit_Id={"*"},Cal_Date={ ">=$(=addmonths(max(Cal_Date),-2))<=$(=max(Cal_Date))"}> distinct Cust_Internal_Id)

Then used * to get the intersection between both set analysis.

Regards

[ElsKnockaert]

Thank you so mucht! This worked like a charm!