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: 
MRitter
Employee
Employee

No idea how to approach this one

The requirement is that I show a bar chart that displays a count of dormant customers for that quarter.  A dormant customer is defined as one that has not ordered in the past 8 quarters.  

My data contains a row for each quarter that the customer purchased.  No row if they bought nothing that quarter.

I have created a field that shows the number of quarters since the last quarter they purchased.

UpdatedBookings:
Load *,
if([End Customer Global Ultimate ID] = Previous([End Customer Global Ultimate ID]),
QuarterIndex - Previous(QuarterIndex)) as peek
Resident Bookings
Order By [End Customer Global Ultimate ID],QuarterIndex asc;

QuarterIndex is the number for the quarter(Current quarter is 43).

Here is a simple table displaying the results for a single customer.

screen shot.png

So in Quarter 38 they were dormant for 12 quarters before this purchase. So I would want a way to count them in the bar for each of the quarters that they were dormant.  Keep in mind that they were not officially dormant until they didn't purchase for 8 quarters.  So I would only count them in 4 of the 12 quarters.  

Any thoughts would be greatly appreciated.

 

5 Replies
JordyWegman
Partner - Master
Partner - Master

Hi M,

I think you can just expand your work with a preceding load:

UpdatedBookings:
Load
  *,
  If(peek - 8 > 0,1,0) as _indDormant
;
Load *,
if([End Customer Global Ultimate ID] = Previous([End Customer Global Ultimate ID]),
QuarterIndex - Previous(QuarterIndex)) as peek
Resident Bookings
Order By [End Customer Global Ultimate ID],QuarterIndex asc;

Then use this formula with set analysis in your bar chart:

Measure: Count({$< _indDormant = {1}>}Customer)
Dimension: Quarter

 

Jordy

Climber

Work smarter, not harder
MRitter
Employee
Employee
Author

Thank you.  That actually tells me when the customer returned from being dormant.  I also needed that.  So that is awesome.

Now I need to be able to count this customer as dormant in the months prior to this month that is  being flagged.  This is there first month back.

They were dormant from month 27 to month 37.  I'll keep thinking about it now that you gave me that great advice and see how I can use this.

JordyWegman
Partner - Master
Partner - Master

Just to be sure, you want to know for every week that a customer is dormant? So count customer A in quarter 27, 28, 29 etc.. and then a accumulation of the number of dormant customers in a quarter?

If you don't have the other quarters, you will need this. Because otherwise you can't see this.. I created two inlines, you can create a table for this. Use this to make all possible combinations and create then an indicator.

Then you can use the indicator with a count/sum for knowing how many are there in a quarter!

//Create a list of all quarters and change ofcourse the inline to a table
Quarters:
Load * Inline [
Quarter
27
28
29
30
31
32
];

//Create a list of all customers and join this to the quarters now you have all the possible combinations.
Join (Quarters)
Load * Inline [
Customers
A
B
];

// Left Join this also based on Quarter and Customer
Left Join(Quarters)
Load
  *,
  Customer as CustomerDormant //This is used for the indicator later
  If(peek - 8 > 0,1,0) as _indDormant
;
Load *,
if([End Customer Global Ultimate ID] = Previous([End Customer Global Ultimate ID]),
QuarterIndex - Previous(QuarterIndex)) as peek
Resident Bookings
Order By [End Customer Global Ultimate ID],QuarterIndex asc;

UpdateBookings:
Load
 *
 If(Isnull(CustomerDormant),1,0) as _indCustomerDormant
Resident Quarters;

drop table Quarters;
drop field CustomerDormant from UpdateBookings;

 Jordy

Climber

Work smarter, not harder
MRitter
Employee
Employee
Author

Thanks again.  The load for the bookings table is missing from this script and having trouble figuring out exactly where in the logic to put it.

JordyWegman
Partner - Master
Partner - Master

Hi,

You can put this in front and drop the table after the additions. Is this more clear? Maybe you can DM the whole script? That would maybe make it a bit easier.

Bookings:
Load
  *
From [YourBookingsSource];

//Create a list of all quarters and change ofcourse the inline to a table
Quarters:
Load * Inline [
Quarter
27
28
29
30
31
32
];

//Create a list of all customers and join this to the quarters now you have all the possible combinations.
Join (Quarters)
Load * Inline [
Customers
A
B
];

// Left Join this also based on Quarter and Customer
Left Join(Quarters)
Load
  *,
  Customer as CustomerDormant //This is used for the indicator later
  If(peek - 8 > 0,1,0) as _indDormant
;
Load *,
if([End Customer Global Ultimate ID] = Previous([End Customer Global Ultimate ID]),
QuarterIndex - Previous(QuarterIndex)) as peek
Resident Bookings
Order By [End Customer Global Ultimate ID],QuarterIndex asc;

drop table Bookings;

UpdateBookings:
Load
 *
 If(Isnull(CustomerDormant),1,0) as _indCustomerDormant
Resident Quarters;

drop table Quarters;
drop field CustomerDormant from UpdateBookings;

Jordy

Climber

Work smarter, not harder