"Stock" of active customers per month

[digitale]

Hello there, 

I'm not new to Qlik, I've already used set analysis for different expressions in several sheets however I'm far from being an expert and I don't know from where to start for my current need.

We have those two tables (to simplify I'm not showing all fields)

Table "Transactions" 

transaction_id	transaction_date	customer_id
1	28/11/2019	1
2	03/12/2019	1
3	15/12/2019	2
4	06/01/2020	3
...	...	...

Table "Customers"

customer_id	customer_name
1	John Doe
2	Jane Smith
3	Michael James
...	...

I want to create a table where dimension will be MonthYear and in which for a given month I will see the Count of active distinct customers, an active customer being a customer that has at least made one transaction in the past 3 months.

So table would look like:

Month	Stock of active customers
07-2019	120
08-2019	150
09-2019	135
10-2019	213
11-2019	216
12-2019	245
01-2020	271

So for exemple for December 2019 we would read "In december 2019 we had 245 'active' customers meaning 245 distinct people having made at least one transaction in October/November/December"

I have tried the following but I doubt it is going anywhere : 

=Count(Distinct if(Aggr(Max(transaction_date),customer_id) < (today() - 90), customer_id))

 I really don't know where to start...

Thanks very much for helping

[Taoufiq_Zarra]

@digitale  May be like this :

=count({<customer_id={"=count({<transaction_date={[>=$(=addmonths(today(),-3))]}>} transaction_id)>0"}>} distinct customer_id) 

One Example :

Transactions:

LOAD * INLINE [
    transaction_id, transaction_date, customer_id
    1, 28/11/2019, 1
    2, 03/12/2019, 1
    3, 15/12/2019, 2
    4, 06/11/2020, 3
];

Customers:
LOAD * INLINE [
    customer_id, customer_name
    1, John Doe
    2, Jane Smith
    3, Michael James
];

In dimension :

=Num(Month(transaction_date),'00')&'-'&Year(transaction_date)

In Measure

=count({<customer_id={"=count({<transaction_date={[>=$(=addmonths(today(),-3))]}>} transaction_id)>0"}>} distinct customer_id)

the output:

[Taoufiq_Zarra]

if befor last 3 month change

=count({<customer_id={"=count({<transaction_date={[>=$(=addmonths(today(),-3))]}>} transaction_id)>0"}>} distinct customer_id) 

to

=count({<customer_id={"=count({<transaction_date={[<=$(=addmonths(today(),-3))]}>} transaction_id)>0"}>} distinct customer_id) 

[digitale]

Hello @Taoufiq_Zarra 

Thanks very much for reaching out and helping!

I've made several tests based on your formula but unfortunately for the moment I still can't get the result I'm looking for. I don't get why when using your formula I'm only seeing one line (11-2020) instead of the 3 different months from my dataset (11-2019, 12-2019 and 11-2020).

I will try explode the formula to see if I get every piece correctly : 

First of all I have replaced today() by max(transaction_date()) so the formula is relative to the current table line do we agree on that point? 

First the part between quote marks: 

=count({<transaction_date={[>=$(=addmonths(max(transaction_date),-3))]}>} transaction_id)>0

 
This is a condition testing whether we have at least one transaction in the period given (3months) right? When I put that in a table it either returns 0 or -1.

Then I'm not getting given the Set Analysis doc how this second part works using the part 1.

<customer_id=%PART1%>

In the end with the formula here is the result I need to read :

MonthYear	Amount of active distinct customers
11-2019	1
12-2019	2
11-2020	1

We would read that for exemple for line 12-2019:

"In the period of 3 months from 31/09/2019 to 31/12/2019, we had 2 distinct customers making at least one transaction" 

Thanks very much for your time,

[Taoufiq_Zarra]

@digitale 

attached qlik file

in dimension =YearMonthtransaction_date

and in measure :

=count(distinct if(transaction_date<=transaction_mp and AddMonths(transaction_mp,-3)<=transaction_date,customer_id))

[digitale]

Hello @Taoufiq_Zarra 

Thanks very much for your time and your explanation through direct messages!

The table provided as solution is the one I am looking for!

Will try to implement this solution in my actual prod data model ASAP 🙂

Have a nice day