Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arixooo123
Creator III
Creator III

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

1 Solution

Accepted Solutions
MarcoWedel

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

View solution in original post

12 Replies
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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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:

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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