How to use Account value to PREVIOUS function of Date in Data action

[Vijay_Dhayalan]

Hello Experts,

We have a requirement where the user wants to input the number of months for delay in payment.

Let’s say we have 3 GLs – Number_of_Month, Payment, Delayed_payment

Number_of_Month – User inputs number of months

Delayed_payment – Previous (Number_of_Month) of Payment

When I write the DA for this like below it is working for a single company code but when I have Number_of_Month for another company code then it sums up the Number_of_Month

INTEGER @A

@A = RESULTLOOKUP([d/GLACCOUNT] = " Number_of_Month, [d/Date] = "000000")

IF RESULTLOOKUP([d/GLACCOUNT] = """ Number_of_Month, [d/Date] = "000000"> 0 THEN

DATA([d/GLACCOUNT] = "Delayed_payment") = RESULTLOOKUP([d/Date] = PREVIOUS (@A, "MONTH"), [d/GLACCOUNT] = "Payment")

ENDIF

I run this for 202412 period

This works as expected when we have value for only one company code.

GL Account	Company Code	Date	Amount
Payment	CC1	202212	100
Payment	CC1	202312	200
Payment	CC2	202212	111
Payment	CC2	202312	222
Number_of_Month	CC1	#	12
Number_of_Month	CC2	#	-
Delayed_payment	CC1	202412	200
Delayed_payment	CC2	202412	-

But when I have months for another Company code it sums up (12+12=24 month) and shows the 202212th value for both Company codes

GL Account	Company Code	Date	Amount
Payment	CC1	202212	100
Payment	CC1	202312	200
Payment	CC2	202212	111
Payment	CC2	202312	222
Number_of_Month	CC1	#	12
Number_of_Month	CC2	#	12
Delayed_payment	CC1	202412	100
Delayed_payment	CC2	202412	111

I have tried using FOREACH and also VARIABLEMEMBER as well but no luck.

Is there any other way to achieve this.

Thanks in advance.

Vijay

[N1kh1l · Accepted solution]

@Vijay_Dhayalan 

The issue is variable @A neither gets overwritten nor cleared and will keep appending for all records. It will only work for single iteration of records. Try with FOREACH with all dimensions which are primary key for record Number_of_Month.

Initial Data

DA Code:

MEMBERSET [d/Measures]="AMOUNT"
MEMBERSET [d/Date]=BASEMEMBER([d/Date] , "2025")
MEMBERSET [d/COMPANY_CODE]=("CC1","CC2")

INTEGER @DelayMonths

FOREACH [d/COMPANY_CODE]
@DelayMonths=0
	@DelayMonths=RESULTLOOKUP([d/GLACCOUNT]="Number_of_Month",[d/Date]="202401") // using 202401 as 0000 not there
	IF @DelayMonths>0 THEN
	DATA([d/GLACCOUNT]="Delayed_payment")=RESULTLOOKUP([d/Date]=PREVIOUS(@DelayMonths),[d/GLACCOUNT]="Payment")
	ENDIF
ENDFOR

DA Output:

No of Month as 12

No of Month as 24

Hope this helps !!

Nikhil

[Vijay_Dhayalan · Accepted solution]

Thanks Nikhil. I tried FOREACH before but with single Dimension. Now I added 2 more Dimension in FOREACH which will obviously hinder the performance, but it worked.