Offset

[Channa]

Hi All 

I have data like below

Company,Period,delay,value,Period

A,Month,1,200,Jan

A,Month,1,200,Feb

A,Month,1,200,Mar

A,Month,1,200,Apr

B,Month,2,400,Jan

B,Month,2,400,Feb

B,Month,2,400,Mar

B,Month,2,400,Apr

if delay is 1 i need to move value one step below Jan should be blank

if delay is 2 i need to move value two steps below Jan and Feb should be blank

any clue

[edwin]

its not obvious what is meant by "move value one step below Jan should be blank", maybe you can provide a table with the expected result based on the data provided above

[stevejoyce]

This should work:

if(num(month(addmonths(date#(Period,'MMM'), -delay))) < num(month(addmonths(date#(Period,'MMM'),0))), month(addmonths(date#(Period,'MMM'), -delay)))

if you have other periodtypes (not month) you can have an if statement to add months, add years or days or whatever your other values are. 

if(PeriodType = 'Period', 

if(num(month(addmonths(date#(Period,'MMM'), -delay))) < num(month(addmonths(date#(Period,'MMM'),0))), month(addmonths(date#(Period,'MMM'), -delay)))

,

If(PeriodType = ...

))

[brunobertels]

hi 

is this what you need : 

if so then your mesure to add is : 

if(num(month(date#(Period1,'MMM'))) - num(delay) > 0,value,'0')

[Channa]

Hi Edwin,

i am looking for expected results column in script level

Account	Scheduled	Fee	Delay	Forecasting	ExpectedResults
A	monthly	200	2	11/1/2021
A	monthly	200	2	12/1/2021
A	monthly	200	2	1/1/2022	200
A	monthly	200	2	2/1/2022	200
A	monthly	200	2	3/1/2022	200
A	monthly	200	2	4/1/2022	200
B	monthly	300	1	11/1/2021
B	monthly	300	1	12/1/2021	300
B	monthly	300	1	1/1/2022	300
B	monthly	300	1	2/1/2022	300
B	monthly	300	1	3/1/2022	300
B	monthly	300	1	4/1/2022	300
C	monthly	400	3	11/1/2021
C	monthly	400	3	12/1/2021
C	monthly	400	3	1/1/2022
C	monthly	400	3	2/1/2022	400
C	monthly	400	3	3/1/2022	400
C	monthly	400	3	4/1/2022	400

[Channa]

HI @brunobertels 

it is not working in case of December 2021 ,Jan 2022

please see same please data

Name.Account	PaymentScheduled	BaseFeeInEUR.BaseFee	ActualInMonths	ForecastingMonths	ExpectedResults
A	monthly	200	2	11/1/2021
A	monthly	200	2	12/1/2021
A	monthly	200	2	1/1/2022	200
A	monthly	200	2	2/1/2022	200
A	monthly	200	2	3/1/2022	200
A	monthly	200	2	4/1/2022	200
B	monthly	300	1	11/1/2021
B	monthly	300	1	12/1/2021	300
B	monthly	300	1	1/1/2022	300
B	monthly	300	1	2/1/2022	300
B	monthly	300	1	3/1/2022	300
B	monthly	300	1	4/1/2022	300
C	monthly	400	3	11/1/2021
C	monthly	400	3	12/1/2021
C	monthly	400	3	1/1/2022
C	monthly	400	3	2/1/2022	400
C	monthly	400	3	3/1/2022	400
C	monthly	400	3	4/1/2022	400

[stevejoyce]

@Channa did you look at my post, first solution proposed?  that should work and can be done in load script as well.

[brunobertels · Accepted solution]

hi 

try this script it works for me 

//////////////////////////////

[Table]:
LOAD * INLINE
[
Name.Account,PaymentScheduled,BaseFeeInEUR.BaseFee,ActualInMonths,ForecastingMonths,ExpectedResults
A,monthly,200,2,11/01/2021,
A,monthly,200,2,12/01/2021,
A,monthly,200,2,01/01/2022,200
A,monthly,200,2,02/01/2022,200
A,monthly,200,2,03/01/2022,200
A,monthly,200,2,04/01/2022,200
B,monthly,300,1,11/01/2021,
B,monthly,300,1,12/01/2021,300
B,monthly,300,1,01/01/2022,300
B,monthly,300,1,02/01/2022,300
B,monthly,300,1,03/01/2022,300
B,monthly,300,1,04/01/2022,300
C,monthly,400,3,11/01/2021,
C,monthly,400,3,12/01/2021,
C,monthly,400,3,01/01/2022,
C,monthly,400,3,02/01/2022,400
C,monthly,400,3,03/01/2022,400
C,monthly,400,3,04/01/2022,400
](delimiter is ',');

TABLE1:
NoConcatenate
Load
Name.Account,
PaymentScheduled,
BaseFeeInEUR.BaseFee,
if(peek([Name.Account],-ActualInMonths)=[Name.Account],
if(addmonths(ForecastingMonths,-ActualInMonths)<ForecastingMonths,BaseFeeInEUR.BaseFee,0)
,
0) as ExpectedResults_1,

ActualInMonths,
date(date#(ForecastingMonths,'MM/DD/YYYY'),'DD/MM/YYYY') as ForecastingMonths,
ExpectedResults
resident [Table];
drop table Table;

/////////////////////////////////////////////

last column is ExpectedResult_1 i added in script like that 

if(peek([Name.Account],-ActualInMonths)=[Name.Account],
if(addmonths(ForecastingMonths,-ActualInMonths)<ForecastingMonths,BaseFeeInEUR.BaseFee,0)
,
0) as ExpectedResults_1,

[Channa]

Hi All i did it in different way

this also works for me added new column as sort

load *,
if(ActualInMonths=0,ToBeInvoiced,

if(ActualInMonths=1,
IF( Sort=1,0,
if(Sort=2,previous(ToBeInvoiced),
if(Sort=3,previous(ToBeInvoiced),
if(Sort=4,previous(ToBeInvoiced),
if(Sort=5,previous(ToBeInvoiced),
if(Sort=6,previous(ToBeInvoiced)
)))))),

if(ActualInMonths=2,
IF( (Sort=1 or Sort =2),0,
if(Sort=3 ,Previous(previous(ToBeInvoiced)) ,
if(Sort =4 ,Previous(previous(ToBeInvoiced)),
if(Sort =5 ,Previous(previous(ToBeInvoiced)),
if(Sort =6 ,Previous(previous(ToBeInvoiced))
))))),

if(ActualInMonths=3,
IF( (Sort=1 or Sort =2 or Sort =3),0,
if(Sort= 4 ,Previous(Previous(previous(ToBeInvoiced))) ,
if(Sort =5 ,Previous(Previous(previous(ToBeInvoiced))) ,
if(Sort =6 ,Previous(Previous(previous(ToBeInvoiced))) )))),

if(ActualInMonths=4,
IF( (Sort=1 or Sort =2 or Sort =3 or Sort = 4),0,
if(Sort= 5 ,Previous(Previous(Previous(previous(ToBeInvoiced)))) ,
if(Sort =6 ,PREVIOUS(Previous(Previous(Previous(ToBeInvoiced))))))),

if(ActualInMonths=5,
IF( (Sort=1 or Sort =2 or Sort =3 or Sort = 4 or Sort = 5),0,
if(Sort= 6 ,Previous(Previous(Previous(Previous(previous(ToBeInvoiced)))))
))))))))

as ExpectedPayment;