Create rolling 4 quarter As of table

[jyothish8807]

Hi All, 

Below is my expected requirement. I have a field YearMonth.

YearMonth	As of filed
201803	201803
201806	201803
201806	201806
201809	201803
201809	201806
201809	201809
201812	201803
201812	201806
201812	201809
201812	201812
201903	201806
201903	201809
201903	201812
201903	201903

[tresesco]

Hi KC,
Could you verify your expected output column ([As of Field]) and explain it's logic ?

[jyothish8807]

Hi Tresesco,

I got it running, like this:

T1:
LOAD * INLINE [
FP
201803
201806
201809
201812
201903
];

AsOfMonth:
load
FP as Month_AsOf,

Autonumber(FP) + 1 - IterNo() as FP

Resident T1
while IterNo() <= 4;

right join load Autonumber(FP) as FP,FP as Date Resident T1;
Drop table T1;

I shuffled the fields in the requirement, my bad 

[tresesco]

Is this working for you? I guess this solution has some glitch. Are you doing this for months or quarters?

[jyothish8807]

Yes, it seems to be working for me. Can you please help me with the glitch ?

I  have to do this for quarters, but i just converted the Qtr to sequential number using Auto number to make it easy 

[jyothish8807]

I then used Month_Asof as my dimension.

[tresesco]

It looks that, your solution works with two conditions:
- all quarter end months have to be present there in the calendar, i.e. - no missing quarter months
- all such months have to be loaded in order, i.e. - load order matters.

[jyothish8807]

Yes, that is true.

Can you please suggest me a better approach.

[tresesco · Accepted solution]

I would probably do it something like this:

AsOfMonth:
load
	//FP as Month_AsOf,
	FP,
	//Autonumber(FP) + 1 - IterNo() as FP,
	If(Month+3*(IterNo()-1)>12, (Year+1)*100+(Month+3*(IterNo()-1)-12),
	FP + 3*(IterNo()-1)) as Month_AsOf

while IterNo() <= 4;

T1:
LOAD *,
	 Left(FP,4) as Year,
     Right(FP,2) as Month
     
     INLINE [
FP
201803
201706
201809
201903
201812
];

[jyothish8807]

Thanks a lot for the solution 🙂

I was struggling with this logic:

If(Month+3*(IterNo()-1)>12, (Year+1)*100+(Month+3*(IterNo()-1)-12),
FP + 3*(IterNo()-1))

So used a short cut 😛