SAC Date Calculation in DA

[Deb_D]

I have a “payment method” dimension with an attribute for “Expected Settlement Time”. Based on the method chosen the attribute can be 1/2/3/4 days (the attribute is set as an integer).

I have a measure for incomes, a measure for outcomes and one created to copy the income in the expected settlement date, named “Expected Outgoing”.

My problem is that I don’t know how to write this calculation - copying the some 3-4 days after based on the attribute.

I’ve tried the below, but the syntax isn’t correct, as it mixes date and number of days:

DATA([d/Measures] = "Expected_Outgoing") = 
RESULTLOOKUP([d/Measures] = "Income", [d/Date] = DATEDIFF( [d/Date],[d/IL_D_POC_RPD_PMNTMTHD].[p/STLMNT_TIME],"DAY" ) )

DATA([d/Measures] = "Expected_Outgoing") =

RESULTLOOKUP([d/Measures] = "Income", [d/Date] = DATEDIFF( [d/Date], [d/IL_D_POC_RPD_PMNTMTHD].[p/STLMNT_TIME], "DAY" ) )

How should I approach that?

[William_Yu1 · Accepted solution]

DATEDIFF will only give difference in number of days instead of a specific date.  You should try something like this: 

DATA([d/Measures] = "Expected_Outgoing") = RESULTLOOKUP([d/Measures] = "Income", [d/Date] = NEXT(ATTRIBUTE([[d/IL_D_POC_RPD_PMNTMTHD].[p/STLMNT_TIME]), "DAY",[d/Date] ))

[Deb_D · Accepted solution]

thank you @William_Yu1 for for reason it doesn't work (I've created an attribute as decimal instead of integer - didn't help, I've added parentheses doesn;t help) - look:

[Deb_D · Accepted solution]

thank you @William_Yu1 for for reason it doesn't work (I've created an attribute as decimal instead of integer - didn't help, I've added parentheses doesn't help) - I don't know how to add a picture in comments here but it has an EOF error

[MoonJun · Accepted solution]

Hi @Deb_D,

Unfortunately, the ATTRIBUTE() function can't be located in the NEXT() function. Advanced formulas do not support it. 

To get your expected result, please refer to the script below.  

INTEGER @Day

FOREACH [d/IL_D_POC_RPD_PMNTMTHD]
	@Day = ATTRIBUTE([d/IL_D_POC_RPD_PMNTMTHD].[p/STLMNT_TIME])
	DATA([d/Measures] = "Expected_Outgoing") = RESULTLOOKUP([d/Measures] = "Income", [d/Date] = NEXT(@Day, "DAY", [d/Date]))
ENDFOR

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

Regards,

Moonjun

[N1kh1l]

@Deb_D 

DATEDIFF might not work. You will have to use NEXT and use ATTRIBUTE to pass the no of days as parameter.

I am assuming you want to copy Income to Expected Outgoing but with an offset on Income date ( read from attribute of payment method)

DATA([d/Measures] = "Expected_Outgoing",[d/Date] = NEXT(ATTRIBUTE([[d/IL_D_POC_RPD_PMNTMTHD].[p/STLMNT_TIME]), "DAY",[d/Date] ))) =RESULTLOOKUP([d/Measures] = "Income")

May be the above might not work as NEXT might not work with ATTRIBUTE within it.

Then You need

FOREACH [d/Paymentmethod]

@Day=ATTRIBUTE([[d/IL_D_POC_RPD_PMNTMTHD].[p/STLMNT_TIME])

DATA([d/Measures] = "Expected_Outgoing",[d/Date] = NEXT(@Day, "DAY",[d/Date] ))) =RESULTLOOKUP([d/Measures] = "Income")

ENDFOR

Nikhil

[Deb_D]

Thank you