Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am looking to create a chart like this:
Total Number | Jan 2012 | Feb 2012 |
---|---|---|
Gold | 10000 | 8000 |
In Feb 2012, I need to know how many of the gold accounts are also gold account in Jan 2012. In March I need to know the same for Feb. so on and so forth until December
I created a vartiable called vPriorMonth, I do not know how to use it in a set analysis.
Any help is appreciated!
I guess we can handle this in script too. We can make a make a composite key like 'Month_Number-Account'.
For Example:
Month Account Composite Key
Jan 123 1-123
Feb 123 2-123
then calulate Account_Flag (IF left(Composite Key-1)&'-'Account) is present than 'Yes' else 'No'
Hope this works
Good suggestion for only 2 months, but when it comes to many months, then we do not know which is which month. Right?
so let's say when it comes to March and account 123 is still active, do we marked it yes. But we do not really know if this yes is to what month.
Also how do you know if the composite key present?
composite key we need to generate for each month which ia a combination of Month and Account.
If you have data for sevral years you can also add year into composite key
Please find attached application.
there must be something I am missing. I know your used (IF left(Composite Key-1)&'-'Account)
to create the Prev_month_com_key but how did you know that key must exist or not?
Can you elabrate your idea?
If previous month key is not present then it will not load those accounts and months.
In youe requirement we just need to concern with accounts which are present in last month.
if I know the previous key is or is not present, I would not have problems...
If you would load the data ordered you can use previous() function in load script to discover retension and set a counter flag on the record you sum up in the chart.
To give you an idea how to discover retention with previous() function:
Input:
LOAD ID,
Period
FROM
D:\Book2.xlsx
(ooxml, embedded labels, table is Sheet1);
Result:
NoConcatenate LOAD ID,
Period,
if(ID=previous(ID) and Period=previous(Period)+1, 1, 0) as Retention
Resident Input
Order By ID, Period;
Drop Table Input;
- Ralf