topic Re: lost customers in QlikView

lost customers

ali_hijazi — Sun, 14 Aug 2016 17:56:15 GMT

Hello

I have the following pivot table in which I'm counting the number of customers who got invoices transactions:

count(distinct {<TRANS_TYPE={'invoice'}>} CUSTOMER_KEY)

what I want now is to add another column which counts the lost customers per month; a lost customer is the one who hasn't got any invoice transaction in the period 12 months prior to each month in the pivot table

so let's take the first business unit (scrambled here) now next to Jan 2016 I want to get the count of customers who haven't got any invoice transaction in the period which is between 1 Feb 2015 and 31 Dec 2015

next to Feb 2016 I want to get the count of customers who haven't got any invoice transaction in the period which is between 1 Mar 2015 and 31 jan 2016 and so on

my problem is in how to get the variable date range per row which depends on the month displayed

so in peudo code the lost customers are the ones excluded in the range of dates which depend on the displayed month year

please advise

Re: lost customers

vinieme12 — Sun, 14 Aug 2016 18:31:54 GMT

if you have numeric YEAR and MONTH Fields, then your comparison periods will be as below

>=Addmonths(MakeDate(Year,Month,1),-11) < (MakeDate(Year,Month,1))

Re: lost customers

vinieme12 — Sun, 14 Aug 2016 18:40:11 GMT

another way would be to add a flag for each customer for each month and then refer the flag in set analysis; should be easier

Re: lost customers

ali_hijazi — Sun, 14 Aug 2016 19:46:59 GMT

you mean something like this?

COUNT

(DISTINCT

{

CUSTOMER_KEY=E(

{

TRANS_DATE={'>=$(=NUM(DAYSTART(ADDMONTHS(MAKEDATE(YEAR,MONTH_NUM,1),-11)))) <$(=NUM(DAYSTART(MAKEDATE(YEAR,MONTH,1))))'}

}CUSTOMER_KEY

)

}

CUSTOMER_KEY)

Re: lost customers

vinieme12 — Mon, 15 Aug 2016 06:44:49 GMT

yes

Re: lost customers

ali_hijazi — Mon, 15 Aug 2016 10:34:43 GMT

may i share with you a sample document?

I got a customer whose name is 3 Chefs

this customer has 11/9/2015 as max date on which he go invoiced

However when I create a chart with Business_Unit_Name, and Month_Year as dimensions and use the above expression it is not being counted

Please advise

Re: lost customers

vinieme12 — Mon, 15 Aug 2016 10:37:48 GMT

yes, please share a sample app

Re: lost customers

ali_hijazi — Mon, 15 Aug 2016 11:12:52 GMT

I uploaded a sample qvw file

please advise

Re: lost customers

ali_hijazi — Mon, 15 Aug 2016 16:37:30 GMT

the above expression that I used has no effect

it is getting the number of customers that are invoiced in the selection of dates

I selected 2016 and the condition which is

CUSTOMER_KEY=E(

{

TRANS_DATE={'>=$(=NUM(DAYSTART(ADDMONTHS(MAKEDATE(YEAR,MONTH_NUM,1),-11)))) <$(=NUM(DAYSTART(MAKEDATE(YEAR,MONTH,1))))'}

}CUSTOMER_KEY

)

has no effect ; the result is the same if I omit this

Re: lost customers

vinieme12 — Mon, 15 Aug 2016 20:09:57 GMT

Sorry, I have been busy. I will definitely look at this and come back to you