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

[marcus_sommer]

I would try as expression something like this:

sum(aggr(if(DATE1 - DATE2 > 240, 1, 0), Customer)

Maybe it's also helpful to cluster this with class(aggr(DATE1 - DATE2, Customer), 30) as calculated dimension and count(distinct Customer) as expression.

- Marcus

[arixooo123]

Dear Marcus,

the expire_date column is  in date format. i like to see the churn trend by month and year. so i guess i need to extract the month and year part of date and set it as the dimension of my bar charts.

and then in expression tab, count the churned customers.

any idea?

thanks buddy

[marcus_sommer]

At the first step I would try to extend the object and the expression by year and month. But maybe a script-approach could be better, maybe something like this:

ChurnDate:

Load Customer, max(Date) + 240 as ChurnDate From xyz Group by Customer;

And as expression in a pivot: count(if(InmonthToDate(ChurnDate, Date, 0), Customer))

- Marcus

[arixooo123]

dear Marcus,

To better know the question, i upload a sample data.

we have 100 customers there. each has an Effective Date (Start) and Expiry Date .

what we need to know is the number of churned customers in each month and year.

In SQL i compare their Expiry date with different months of the year. From January to December.

like this :

SUM ( CASE when DATEDIFF ( Day,EXPIRY_DATE , convert ( datetime,'2014-01-31',120 ) )>240 then 1 else 0 end ) as  ChurnCustomersInJanuary

but i want to see this in a dashboard. how to add a virtual timeline and count number of churned customers in each month. I need a dimension for months. but i cannot use Effective or Expiry Date for that.

[arixooo123]

Now that i think, I need a calculated dimension for months.

Calculated Dimension:

(January,February,....December) as MonthTrend 

and in Expression tab:

Sum(aggr ( If(Num(ExpiryDate)-Num(MonthTrend)>240  [Sub_ID]) ))

[marcus_sommer]

If possible I wouldn't use a calculated dimension for the period and try it rather within the script, maybe like in the attachment.

- Marcus

[MarcoWedel]

Hi,

one solution could be:

tabCustDates:
LOAD CustomerID,
     DayName(EFF_DATE) as EFF_DATE,
     DayName(EXP_DATE) as EXP_DATE,
     Date(EXP_DATE+240) as CHURN_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]

Dear Marco, I really appreciate your help. just to make sure we are on the same page and understand each other i calculated the churn result by query. please see the attached file.

as you can see, number of churn customers in (for example)

Feb 2014 = Number of Churn customers in Jan 2014 and before that + Number of Churn Customers in Feb 2014

[arixooo123]

and to be sure we know what we are looking for,

In our definition, a churn customers is whom 240 days has passed since the his/her Expiry Date.

So Customers who their Expiry Date is 240 days or more before an specific date are known as churn customers.

This way, we couldn't possible have any new churn customers in Second Half of 2014,2015 or 2016.

I guess we need to calculate ChurDate as  : Date(EXP_DATE MINUS 240) as CHURN_DATE