topic Re: Lost/Inactive customers in QlikView

Lost/Inactive customers

madhuqliklondon — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Lost/Inactive customers

CarmenReilly — Thu, 12 Jul 2018 16:11:54 GMT

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.

Re: Lost/Inactive customers

Anonymous — Thu, 12 Jul 2018 18:02:25 GMT

Hi Madhu,

Can you share sample data In an excel ot txt?

Re: Lost/Inactive customers

madhuqliklondon — Fri, 13 Jul 2018 08:05:35 GMT

Hi John,

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

Thank you

Re: Lost/Inactive customers

qlikviewwizard — Fri, 13 Jul 2018 09:09:36 GMT

Hi,

Is this you are expecting?

Re: Lost/Inactive customers

madhuqliklondon — Fri, 13 Jul 2018 09:20:07 GMT

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.

Re: Lost/Inactive customers

qlikviewwizard — Sat, 14 Jul 2018 02:14:39 GMT

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.

Re: Lost/Inactive customers

Anonymous — Sat, 14 Jul 2018 08:13:02 GMT

Hello Madhu,

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

Re: Lost/Inactive customers

qlikviewwizard — Tue, 17 Jul 2018 08:47:06 GMT

Hi madhuqliklondon

Did you get the correct answer?

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

Thank you.

Re: Lost/Inactive customers

madhuqliklondon — Mon, 23 Jul 2018 14:01:40 GMT

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.

Re: Lost/Inactive customers

qlikviewwizard — Tue, 24 Jul 2018 01:15:55 GMT

Hi madhuqliklondon

This is separate question.

Please post this as a new discussion and tag me as qlikviewwizard

Please mark Correct Answer and close this thread.

Thank you.

Re: Lost/Inactive customers

madhuqliklondon — Tue, 24 Jul 2018 08:50:33 GMT

Hi Arjun,

In the sample app you have provided ,lost coustomer count in June 2018 it is 2 . When you clikc on it customer id's 4 & 5 are highloghted. Buy they have made a purchase in Jan 18. When we are checking last 365 days no sales they shouldn't be counted. Could you please check.Thank for your time Arjun.

Re: Lost/Inactive customers

qlikviewwizard — Tue, 24 Jul 2018 08:58:18 GMT

Hi madhuqliklondon

You are confusing.Order Date is showing June -2018 itself. Please check this updated script and output.

Re: Lost/Inactive customers

vipin_mishra479 — Tue, 24 Jul 2018 10:01:04 GMT

Hi Madhu,

for this you will try to create a modified script. Like Create a customer wise monthly status(Flag) is this Active or Inactive and use that flag into Expression.Hope for this you achive your output.