How do I create a chart like this?

[alexpanjhc]

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!

[#2]

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

[alexpanjhc]

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?

[#4]

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.

[alexpanjhc]

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?

[#6]

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.

[alexpanjhc]

if I know the previous key is or is not present, I would not have problems...

[rbecher]

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.

[rbecher · Accepted solution]

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