Data filtering in table

[Anonymous]

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

[stevedark]

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

[Anonymous]

Thank you