Distribute amount to payment date based on invoice date using SAC data action script

[Astra1562]

I have use case for manual forecasting from excel sheet to SAC where based on each invoice date needs to have 30 days in payment date having same invoice amount for each payment date. I tried using FOR EACH AND NEXT but it not as per expected format. Attached screenshot. Thank you

Note: We are using Invoice date and payment date in model. Happy to get ideas on how DSO can replace the manual way of forecasting.

[MoonJun]

Hi @Astra1562

I am not clear about your expectations from the attached screenshot.

Please provide the Source data(fact data) and your expected result based on the source data (table format).

Regards,

Moonjun

[Astra1562]

Hi MoonJun,

The source data contains invoice amount, invoice date and company code. Then based on each fixed payment day (1st monday of 2ndweek ) the invoice amount is distributed equally for 30 days. It has another manual input table ( Payment Days & % ) for each company code from where it picks up data to calculate the amount days in expected layout table. 

[MoonJun · Accepted solution]

Hi @Astra1562 

Please refer to the script below. 

MEMBERSET [d/Company_Code] = "A"
MEMBERSET [d/Date] = "2025-01-01" TO "2025-01-31"
MEMBERSET [d/Region]="EMEA"

INTEGER @Counter
INTEGER @PaymentDays

@PaymentDays = %Payment_Days%-1

FOR @Counter = 0 to @PaymentDays
	DATA([d/Measures]="InvoiceAmount",[d/PaymentDate]=NEXT(@Counter,"DAY",%Start_Payment%)) = RESULTLOOKUP([d/Measures]="InvoiceAmount",[d/PaymentDate]="")
ENDFOR

I hope this is helpful to you, and if I have misunderstood anything, please feel free to reach out.

Moonjun

[Astra1562 · Accepted solution]

Thank you Moonjun. it worked as per the script provided.