12 Replies Latest reply: Feb 1, 2015 5:37 AM by Marco Wedel RSS

    Count and Case to comparing dates in qlikview

    ar eiuiew

      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

          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
            Marco Wedel

            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

              • Re: Count and Case to comparing dates in qlikview
                ar eiuiew

                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
                  ar eiuiew

                  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
                      Marco Wedel

                      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