New and Lost Customers - Urgent Help Required

[Anonymous]

Hi,

I have a query where I need to find out new and lost customers on a monthly basis.

New - A customer which did not have a record in last month sales data.

Lost - A customer which had a record in last month's sales data but doesn't have one in this month.

I have found a few solutions on forum here, however, they are not giving me a monthly list, I need to select a month then the numbers come up for only those months. I am looking for solutions that I can work in expressions in the chart and not by making changes in the script.

I have attached excel of sample data of how I pull records into QlikView. It is from ODBC with sql however I have attached similar excel of how it looks like and how it is structured.

I have also attached a qvw file which shows the results that I am getting from some solutions on forum here. In the file, you will notice a few expressions I have tried from the solutions but are not working correctly. The most appropriate expression is of "Test 3" however it is not showing me all the months, only the latest one.

I need something that looks like the below table (Numbers in Sample table below are made up and may not match with data, it is just a representation of how I want it to look like):

ExtractDate	New Customers	Lost Customers
30/09/2017	110	90
31/10/2017	70	80
30/11/2017	50	100
31/12/2017	25	35
31/01/2018	32	20
28/02/2018	80	33
31/03/2018	90	100
30/04/2018	80	120
31/05/2018	150	20
30/06/2018	180	55

Please advise how can this be done within expression or in the script, either is fine with me, however I will prefer something in expression.

Thank You in Advance.

Regards,

Roopali

[Gysbert_Wassenaar]

It's possible, but I would opt for a script based solution myself. You'll need expressions like this:

pick(match([Extract Date] , $(=concat(DISTINCT chr(39) & [Extract Date] & chr(39), ',', [Extract Date]))),

$(=concat(distinct '$' & '(=count({<[Extract Date]={' & chr(39) & [Extract Date] & chr(39) & '},[Customer Number]=E({<[Extract Date]={' & chr(39) & MonthEnd([Extract Date],-1) & chr(39) & '}>}[Customer Number])>}distinct [Customer Number]))' , ', ', [Extract Date]))

)

I don't blame you if you don't understand that expression.

sillytricksdepartment‌

[Anonymous]

gwassenaar‌ Thank you!

It is really helpful. It does work but I need some more advise so I get the correct data.

I need clarifications on following:

- How do I add a condition for one of the fields, lets say the product should be A only (Not in filters but in the expression itself).

- What will be your solution if done in script, may be thats a better way to do it. I would love to try that solution as well.

Thanks again!