Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

arixooo123
Not applicable

Count and Case to comparing dates in qlikview

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

Tags (3)
1 Solution

Accepted Solutions
MarcoWedel
Not applicable

Re: Count and Case to comparing dates in qlikview

Hi,

I changed the script to only calculate churn dates up to today and changed the chart to full accumulation:

QlikCommunity_Thread_150282_Pic6.JPG

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

12 Replies
marcus_sommer
Not applicable

Re: Count and Case to comparing dates in qlikview

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
Not applicable

Re: Count and Case to comparing dates in qlikview

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
Not applicable

Re: Count and Case to comparing dates in qlikview

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
Not applicable

Re: Count and Case to comparing dates in qlikview

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
Not applicable

Re: Count and Case to comparing dates in qlikview

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
Not applicable

Re: Count and Case to comparing dates in qlikview

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
Not applicable

Re: Count and Case to comparing dates in qlikview

Hi,

one solution could be:

QlikCommunity_Thread_150282_Pic1.JPG

QlikCommunity_Thread_150282_Pic2.JPG

QlikCommunity_Thread_150282_Pic4.JPG

QlikCommunity_Thread_150282_Pic3.JPG

QlikCommunity_Thread_150282_Pic5.JPG

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
Not applicable

Re: Count and Case to comparing dates in qlikview

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
Not applicable

Re: Count and Case to comparing dates in qlikview

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