Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ali_hijazi
Partner - Master II
Partner - Master II

lost customers

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)

community.png

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

I can walk on water when it freezes
9 Replies
vinieme12
Champion III
Champion III

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))

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
ali_hijazi
Partner - Master II
Partner - Master II
Author

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)

I can walk on water when it freezes
vinieme12
Champion III
Champion III

yes

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
ali_hijazi
Partner - Master II
Partner - Master II
Author

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

I can walk on water when it freezes
vinieme12
Champion III
Champion III

yes, please share a sample app

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
ali_hijazi
Partner - Master II
Partner - Master II
Author

I uploaded a sample qvw file

please advise

I can walk on water when it freezes
ali_hijazi
Partner - Master II
Partner - Master II
Author

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

I can walk on water when it freezes
vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.