Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dears,
I have written a query in database which works like a charm. here is the query :
select
SUM ( CASE when DATEDIFF ( Day,EXPIRY_DATE , convert ( datetime,'2014-01-31',120 ) )>240 then 1 else 0 end ) as Churn Customers in January
from TABLE
as you can see, we define a customers as churned when 240 days has passed since the expiry date of his/her last service purchase.
i have a Expiry Date Column which i guess i can extract month and year part of the date.
I like to see the churn trends in dashboard.
let's say i add a line chart, in dimension i add a calculated dimension :
datepart(month,ExpiryDate) ------> you revise the syntax
in expression i should add something like :
count(case when DATEDIFF ( Day,EXPIRY_DATE , convert ( datetime,'2014-01-31',120 ) )>240 CustomerID )
So many thanks in advance
Hi,
I changed the script to only calculate churn dates up to today and changed the chart to full accumulation:
tabCustDates:
LOAD *,
If(EXP_DATE+240<=Today(),Date(EXP_DATE+240)) as CHURN_DATE;
LOAD CustomerID,
DayName(EFF_DATE) as EFF_DATE,
DayName(EXP_DATE) as EXP_DATE
FROM [http://community.qlik.com/servlet/JiveServlet/download/704900-147442/temp100.xlsx] (ooxml, embedded labels, table is Sheet1);
MasterCalendar:
LOAD *,
Day(CanDate) as Day,
WeekDay(CanDate) as WeekDay,
Week(CanDate) as Week,
WeekName(CanDate) as WeekName,
Month(CanDate) as Month,
MonthName(CanDate) as MonthName,
Dual('Q'&Ceil(Month(CanDate)/3),Ceil(Month(CanDate)/3)) as Quarter,
QuarterName(CanDate) as QuarterName,
Year(CanDate) as Year,
WeekYear(CanDate) as WeekYear;
LOAD Date(MinDate+IterNo()-1) as CanDate
While MinDate+IterNo()-1<=MaxDate;
LOAD RangeMin(Min(EFF_DATE),Min(EXP_DATE),Min(CHURN_DATE)) as MinDate,
RangeMax(Max(EFF_DATE),Max(EXP_DATE),Max(CHURN_DATE)) as MaxDate
Resident tabCustDates;
tabLink:
CrossTable(DateType, CanDate)
LOAD CustomerID,
EFF_DATE as effective,
EXP_DATE as expired,
CHURN_DATE as churned
Resident tabCustDates;
hope this helps
regards
Marco
Thanks Man!
You're welcome
regards
Marco