Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Thank you