Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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,
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:
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!