Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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!