topic Re: Count and Case to comparing dates in qlikview in QlikView

Count and Case to comparing dates in qlikview

arixooo123 — Sat, 31 Jan 2015 09:12:47 GMT

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

Re: Count and Case to comparing dates in qlikview

marcus_sommer — Sat, 31 Jan 2015 09:30:18 GMT

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

Re: Count and Case to comparing dates in qlikview

arixooo123 — Sat, 31 Jan 2015 10:03:32 GMT

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

Re: Count and Case to comparing dates in qlikview

marcus_sommer — Sat, 31 Jan 2015 10:47:20 GMT

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

Re: Count and Case to comparing dates in qlikview

arixooo123 — Sat, 31 Jan 2015 13:41:15 GMT

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.

Re: Count and Case to comparing dates in qlikview

arixooo123 — Sat, 31 Jan 2015 14:24:41 GMT

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

Re: Count and Case to comparing dates in qlikview

marcus_sommer — Sat, 31 Jan 2015 17:49:33 GMT

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

Re: Count and Case to comparing dates in qlikview

MarcoWedel — Sun, 01 Feb 2015 01:30:27 GMT

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

Re: Count and Case to comparing dates in qlikview

arixooo123 — Sun, 01 Feb 2015 05:23:25 GMT

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

Re: Count and Case to comparing dates in qlikview

arixooo123 — Sun, 01 Feb 2015 05:43:46 GMT

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

Re: Count and Case to comparing dates in qlikview

MarcoWedel — Sun, 01 Feb 2015 10:34:17 GMT

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

Re: Count and Case to comparing dates in qlikview

arixooo123 — Sun, 01 Feb 2015 10:36:44 GMT

Thanks Man!

Re: Count and Case to comparing dates in qlikview

MarcoWedel — Sun, 01 Feb 2015 10:37:54 GMT

You're welcome

regards

Marco