Setanalysis and Aggr to get Inactive customers

[cvillar]

Hello all,

I want to monitor “Inactive customers in the last 100 days” . For me inactive customer is  a customer that has purchased less than 1000€ cumulated. To get this I thought that maybe I can do this logic:

I can get all history active customers , minus the last 100 days backward active customers.

Then the result are the customers that has not purchased more than 1000€ cumulated in 100 days backward since the day in the chart because tha dimension in chart is the “Order_Date”, then I can see how the customers are activating or deactivating sales based in 100 days back in every single day.

I have this setnalysis but the second part I’m not able to get it working.

Do you have an idea what I’m doing wrong?

//all history customers with more than 1000€ total orders
=count({1<Order_Customer_number = {"=sum(Order_Total_Value_Net)>1000"}>} distinct TOTAL Order_Customer_number)
-
//The last 100 days back with more than 1000€ cumulated
rangesum(above(aggr(count({$<Order_Customer_number = {"=sum(Order_Total_Value_Net)>1000"}>} distinct Order_Customer_number),Order_Order_Date)),0,100) 

Thanks in advance if you can bring light to me.

[cvillar]

Hellom

Any help would be appreciated with this.

Thanks so much!

[stevedark]

Hi @cvillar 

I'm not sure how Order_Customer_number = {"=sum(Order_Total_Value_Net)>1000"} is working, if it is working. If you are accumulating like that you are going to need to use an IF statement, rather than set analysis.

In order to look at each customer number at a time you are going to be needing an aggr on the customer number.

So you will be starting with something like:

sum(aggr(if(sum(Order_Total_Value_Net) > 1000 and sum({<... last 100 days ...>}Order_Total_Value_Net) = 0, 1, 0), Order_Customer_number))

Now, where things start getting tricky is having the rolling periods. I would think carefully about the logic here and what is useful to show. Do you want to count a customer as lapsed in every day from the point they lapsed forward, or just capture the point in time they go 100 days without an order?

There are two ways I might approach getting the rolling days. One of these I documented in a blog post. This is creating a table of dates which links many days (in this case a rolling 100) to a single date which appears on the chart. This blog post is here:

https://www.quickintelligence.co.uk/qlikview-accumulate-values/

The other is to aggregate some of the values in the load script, potentially by using a RESIDENT load with an ORDER BY on customer number and date, then using PEEK to aggregate prior values to give the rolling 100 days total for every day for every customer.

You can also get totals with a RESIDENT and a GROUP BY:

LOAD
  Order_CustomerNumber,
  sum(Order_Total_Value_Net) as CustomerTotalValue
RESIDENT TableName
GROUP BY Order_Total_Value_Net;

Pre-aggregating in the load script can make front-end expressions easier, more efficient, and in some cases possible when it may otherwise not work at all.

Hopefully one of those ideas will point you in the right direction of a solution.

Steve

[agigliotti]

Hi @cvillar ,

What is your chart dimension and measure?

What is your actual result and the expected one?