7 Replies Latest reply: Jan 31, 2014 8:56 AM by Nicole Smith RSS

    Customer Status

      I have a table that has the following

      Customer ID

      Status

      Status StartDate

      Status EndDate

       

       

      There are 4 different status categories, Active, On Hold, Pending, Inactive.

       

      How can I create charts that show the average duration that a customer stays in each category, and also a count of customers by status, over time?

       

      Thanks!

        • Re: Customer Status
          Nicole Smith

          Take a look at the attached .qvw file.  Make sure to look at the load script as I had to add to use IntervalMatch to add a calendar table to your start and end dates (the synthetic key that IntervalMatch creates is fine, and should be there).  The charts should also be calculating what you're looking for.

            • Re: Customer Status

              Thanks so much!

              • Re: Customer Status
                Arjun Arjun

                Hi,

                 

                Thanks for the reply.

                I'm using personal edition, I can't open any qvd, can u please post the expression to plot chart.

                 

                Thanks in Advance

                Pavan.

                  • Re: Re: Customer Status
                    Nicole Smith

                    Load Script:

                    Data:

                    LOAD * INLINE [

                        Customer ID, Status, Status StartDate, Status EndDate

                        1, Active, 1/1/2014, 1/7/2014

                        1, On Hold, 1/8/2014, 1/10/2014

                        1, Pending, 1/11/2014, 1/20/2014

                        1, Inactive, 1/20/2014, 1/25/2014

                        2, Active, 1/1/2014, 1/10/2014

                        2, On Hold, 1/10/2014, 1/15/2014

                        2, Pending, 1/15/2014, 1/21/2014

                        2, Inactive, 1/21/2014,

                    ];

                     

                    //Finding min and max dates so we can create a calendar

                    MinAndMax:

                    LOAD RangeMin(min([Status StartDate]),min([Status EndDate])) as MinDate,

                        RangeMax(max([Status StartDate]),max([Status EndDate])) as MaxDate

                    RESIDENT Data;

                    let MinDate = num(Peek('MinDate'));

                    let MaxDate = num(Peek('MaxDate'));

                    DROP TABLE MinAndMax;

                     

                    //Creating the calendar

                    Calendar:

                    Load Date($(MinDate)-1+iterno()) as Date autogenerate 1 While iterno() <= $(MaxDate)+1 - $(MinDate) ;

                    let MinDate = null();

                    let MaxDate = null();

                     

                    //Joining the calendar dates using interval match to the start and end dates

                    IntervalMatch(Date)

                    LOAD [Status StartDate], [Status EndDate] RESIDENT Data;

                     

                    Type: Pivot Table

                    Dimension 1: Customer ID

                    Dimension 2: Status

                    Expression: avg([Status EndDate]-[Status StartDate])

                     

                    Type: Straight Table

                    Dimension: Customer ID

                    Expression 1: avg({<Status={'Active'}>}[Status EndDate]-[Status StartDate])

                    Expression 2: avg({<Status={'Inactive'}>}[Status EndDate]-[Status StartDate])

                    Expression 3: avg({<Status={'On Hold'}>}[Status EndDate]-[Status StartDate])

                    Expression 4: avg({<Status={'Pending'}>}[Status EndDate]-[Status StartDate])

                     

                    Type: Line Chart

                    Dimension 1: Date

                    Dimension 2: Status

                    Expression: count([Customer ID])

                • Re: Customer Status
                  Gysbert Wassenaar

                  You can calculate the duration by subtracting the StartDate from the EndDate. For example:

                   

                  Load CustomerID,

                  Status,

                  Status StartDate,

                  Status EndDate,

                  interval([Status EndDate] - [Status StartDate],'d') as Duration

                  From ...mysource...;

                   

                  You can then create a straight table object in the front end with Customer ID (and Status) as dimension(s) and as expression avg(Duration).

                   

                  For counting customers by status over time you need to create reference dates for each status. See this blog post for an explanation of how to do that: Creating Reference Dates for Intervals