2 Replies Latest reply: May 24, 2018 4:23 PM by Carlos Rivadeneira RSS

    Data filtering in table

    Carlos Rivadeneira

      I would like to know if someone can help me: - I have expired subscriptions in one table, in another the management of renewals, I want to create a table where the user can see the expiration date when the expiration date was renewed. I put some sample data:


      TABLE 1:

      SUSCRIPTION     EXP_DATE
      SUS01                    01/01/18

      SUS02                    01/02/18

      SUS03                    01/02/18

      SUS04                    01/04/18

       

      TABLE 2:

      SUSCRIPTION    RENEW_DATE

      SUS01                   01/04/18

      SUS02                    01/04/18

      SUS03                    01/04/18

      SUS04                    01/0418

       

      TABLE 3:(RESULT)

      MONTH_EXP          RENEWALS

      JAB                    1

      FEB                    1

      MAR                    1

      APR                    1

       

      Thank you very much for your help

        • Re: Data filtering in table
          Steve Dark

          You can just load both tables and create the table you want from that, so for renewals;

           

          LOAD

            1 as RenewCount,

            SUBSCRIPTION,

            RENEW_DATE,

            Date(MonthStart(RENEW_DATE), 'MMM-YY') as [Month Year]

          FROM TABLE2;

           

          You can then create a table with a dimension of [Month Year] and an expression of sum(RenewCount) and this should work out.  Note the date in the table needs to be an actual date, otherwise you will need to do a Date# to convert it.  Also note, I have kept month and year, or after 12 months you will count two different months worth of data in each month.

           

          You can go beyond this by concatenating both tables into one, and putting a label on as to what type of data you have:

           

          MainData:

          LOAD

            1 as Counter,

            1 as RenewCount,

            0 as ExpiryCount,

            'Renewal' as RowType,

            SUBSCRIPTION,

            RENEW_DATE as Date,

            Date(MonthStart(RENEW_DATE), 'MMM-YY') as [Month Year]

          FROM TABLE2;

           

          CONCATENATE(MainData)

          LOAD

            1 as Counter,

            0 as RenewCount,

            1 as ExpiryCount,

            'Expiry' as RowType,

            SUBSCRIPTION,

            RENEW_DATE as Date,

            Date(MonthStart(RENEW_DATE), 'MMM-YY') as [Month Year]

          FROM TABLE2;

           

          This then allows you to do more analysis, as you have a single date field which things happen against.  So, you could have a dimension of [Month Year] and the following expressions:

           

          sum(RenewCount)

          sum(ExpiryCount)

          sum(RenewCount) - sum(ExpiryCount)

          sum(ExpiryCount) / sum(RenewCount)

           

          These four expressions give you a fuller picture of the month, with Renewals and Expirys separated, as well as your net movement and the percentage shift.

           

          Hope that helps.

          Steve