Count and Case to comparing dates in qlikview

[arixooo123]

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

[MarcoWedel · Accepted solution]

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

[arixooo123]

Thanks Man! 

[MarcoWedel]

You're welcome

regards

Marco