Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In Qlikview 8, Based on a Customer List and number of products purchased by them month on month, I need to categorize the customers into the following categories.
Active – Customers who have been purchasing products month on month basis CONTINUOUSLY
Inactive – customers who are purchasing products but NOT CONSISTENT. This customer should not have activities at least in the last 30 days.
Returning – Customer Purchased a product initially and thereafter no activity
New – No Purchase by the customer yet. Customer just created or yet to start any transaction.
Dimensions used:
CustomerName
Year
Month
Expression:
Sum(Quantity)
Note: I am using QV8 and set analysis wont work. The above categories should be set based on year and month selection.
Sample Output:
Yr --> | 2016 |
| ||||
Mnth --> | 1 | 2 | 3 | 4 | 5 |
|
Customer Name | Status | |||||
ABC Technologies | 45 | 60 | 20 | 35 | 81 | Active |
BDB Technologies | 50 | 23 | 50 | - | - | Inactive |
BDB Technologies | 50 | - | 50 | - | 20 | Inactive |
DHA Technologies | 86 | - | - | - | - | Returning |
EAB Technologies | - | - | - | - | - | New |
.
Active: count(distinct Mnth) = count(distinct total Mnth)
Inactive: count(distinct Mnth) < count(distinct total Mnth) and max(date_of_purchase) < today() - 30
Returning: count(date_of_purchase) =1 and max(date_of_purchase) < today() - 30
New: count(date_of_purchase) = 0 and count(date_of_creation) =1
Hi Gysbert,
Thanks for sharing your thoughts...
I was happy with the output yet not happy with the presentation. You see i am trying to achieve this using a pivot table, as mentioned in my requirement - Year and month column will be pivoted.
I used the suggestions you gave me under expression and i got status columns for all the months and years pivoted.
If i use it in the dimension, i can see only total of the entire value and not aggr data or row wise data...
I need to see only one status column at the begining/end.
Any ideas?
Can some please share set analysis expression for obtaining the customer status? Really stuck up with this for few weeks now...