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: 
MTS95
Contributor III
Contributor III

Display a Year Value in a Field During a Year When a Customer Did Not Purchase Anything

Hi there,

I'm trying to make a table that displays a year value for customers, even when they have not made any purchases. This has been pretty difficult for me so far, since the dataset only loads customers if they have made a purchase and excludes them and all their data entirely if they haven't made a purchase that year.

Here is what the data looks like now:

Customer ID Purchase Year Product
0001 2016 Fishing License
0001 2018 Fishing License
0001 2019 Crabbing License
0002 2018 Fishing License
0002 2019 Crabbing License
0002 2020 Crabbing License
0002 2021 Fishing License
0002 2022 Fishing License
0003 2020 Fishing License
0003 2021 Crabbing License

 

I need the table to display customers even when they did not purchase a product, preferably from the very first year they did to the present year:

Customer ID Purchase Year Product
0001 2016 Fishing License
0001 2017 -
0001 2018 Fishing License
0001 2019 Hunting License
0001 2020 -
0001 2021 -
0001 2022 -
0002 2018 Fishing License
0002 2019 Hunting License
0002 2020 Hunting License
0002 2021 Fishing License
0002 2022 Fishing License
0003 2020 Fishing License
0003 2021 Hunting License
0003 2022 -

 

I'm attempting to get the churn rate of customers (or the % of customers we have not seen return) for each year. I've made something similar in the past (a field that shows when a customer has returned after being absent for a year or multiple years), but I'm struggling with this one. Please let me know if this is a possibility.

Thanks!

2 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi there,

It looks like you need to generate a table with all possible combinations of the Customer ID and the Year, and then join the actual purchases into the table. In order to achieve this, you need to join the two distinct lists with no common key, which creates a Cartesian join (everything joins to everything). So, your process will be as follows:

1. Load a DISTINCT list of customer IDs from the database

2. Generate the list of desired years, using Autogenerate, or a WHILE load

3. JOIN the list of years with the list of Customer IDs. This will generate a list of all possible combinations of Customer ID and Year values.

4. Join into this table the actual purchases. Make sure that Customer ID and Year fields are named identically, so that they can serve as JOIN keys. 

This will generate the list that you wanted to see.

While you are joining, join us for the Masters Summit for Qlik, to learn advanced Qlik development techniques - we will be in Madrid, Spain in September, and in New Orleans, LA in November:
https://masterssummit.com

Cheers,

MTS95
Contributor III
Contributor III
Author

Thank you for your quick response! I've been trying to use the AutoGenerate function to make a list of year per the instructions from this Qlik Design Blog post , although I'm not having quite as much success as I'd hoped. Here is what my script looks like so far:

Let vMinYear = '2012';
Let vMaxYear = Year(Today());

[sales z]:
Load

if(RowNo() = 1, Date#($(vMinYear), 'YYYY'), Date#($(vMinYear) + RowNo(), 'YYYY')) as TheYear

AutoGenerate(vMaxYear - vMinYear + 1)
While $(vMinYear) + RowNo() < $(vMaxYear);

Load Distinct

CustomerId;

SQL Select

CustomerId

The output looks like this in the data model viewer:

MTS95_0-1659537392534.png

It is successfully outputting a list of years since 2012 (the year we began recording data), but for whatever reason it skips 2013. Why is that?

Also, I was hoping that having a Load Distinct statement in the [sales z] table would put the field CustomerId into that same [sales z] table, but I suppose that's where the JOIN statement you mentioned will come into play, correct?

I'm grappling with the syntax of the JOIN statement right now, but I'll update this post if/when I figure it out. Feel free to let me know if you have any pointers for me!

Thanks again!