topic Re: Set Customer status in a Pivot table in QlikView

Set Customer status in a Pivot table

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set Customer status in a Pivot table

Gysbert_Wassenaar — Fri, 02 Sep 2016 11:46:47 GMT

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

Re: Set Customer status in a Pivot table

Wed, 07 Sep 2016 10:50:21 GMT

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?

Re: Set Customer status in a Pivot table

Fri, 09 Sep 2016 09:05:54 GMT

Can some please share set analysis expression for obtaining the customer status? Really stuck up with this for few weeks now...