Lost/Inactive customers

[madhuqliklondon]

Hi All,

I want to find total inactive customers for last 12 months on month basis which means they have placed order before 12 months back and became in active. So want calculate this every month basis to under stand how inactive customers are growing month by month. if I run the query in July it should go back 365 days from the previous month end and give total number of inactive customers. I want to do this month by month.

Thanks for your time in advance.

below are the expected results

month	inactive customers
Jan-18	1
Feb-18	0
Mar-18	0
Apr-18	1
May-18	2
Jun-18	2

[CarmenReilly]

Hi Madhu!

You should be able to do this using a calculated dimension: https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Straight_Table.htm (scroll down to the Chart Properties: Dimensions section).

All the Best,

Carmen

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

[Anonymous]

Hi Madhu,

Can you share sample data In an excel ot txt?

[madhuqliklondon]

Hi John,

Please see the attachment . Sheet 1 is sample data and sheet 2 is expected results.

Thank you

[qlikviewwizard]

Hi,

Is this you are expecting?

[madhuqliklondon]

Hi Arjun,

This expression is giving only results for 2018, I want this to work for back years as well. In example I have just mentioned 2017 and 2018 as sample data. I have years going back to 2007.

[qlikviewwizard]

If you want all the data:

Remove Year={Max(Year)} condition in the set analysis. Then you will get all other Years data.

If you want see 2017 and 2018 (Max 2 Years data) :

COUNT ({$<Year={$(=Max(Year)), $(=Max(Year)-1)}>}  Cusomer Id)

Please use this. You will get the required answer.

[Anonymous]

Hello Madhu,

Can you give some explanation about the expected out put , how did you achieve in Excel?

[qlikviewwizard]

Hi madhuqliklondon

Did you get the correct answer?

If yes, please select Correct Answer and close the thread.

Thank you.

[madhuqliklondon]

Hi Arjun,

I would like to see past 10 years data ,Because data in QVD's available from 2007. Is there way ?

Examples Where:

Lapsed Customers: = count of CustomerIDs which had no sales activity in the 12 months up to and including the specified month (in first column above), AND did have >0 activity in the one month before that .

I.e. Customers counted as lapsed in July 2018 would all have purchased in last June 2017 , then nothing in next 12 months

Reactivated Customers:= count of CustomerIDs which had >0 sales activity in the specified month, AND no sales activity in the previous 12 months before that.

I.e. Customers counted as reactivated in July 2018 would all have purchased in July 18, but nothing in Last 12 months.

A customer might lapse and reactivate a number of times through the time period.