cancel
Showing results for 
Search instead for 
Did you mean: 

Retrieve a set of members as the difference between two dates in properties

fulmil
Explorer
0 Kudos
548

Hello

I am trying to set up an advanced formulas script inside a data action to copy data from member "000000" of the Date dimension to a dynamic set of members of the Date dimension, which must correspond to the months between two dates (start date and end date). The start date and end date are both properties of another dimension (PRICELIST), and will vary by member. As for the rest of the dimensions, the values will be the same as the ones copied:

How can I retrieve the list of members of the Date dimension on which to write the data copied from "000000"?

I tried the following code:

DATA ([d/Measures] = "LIST",

[d/Date] = PERIOD ([d/PRICELIST].[p/VALID_FROM])

TO

PERIOD ([d/PRICELIST].[p/VALID_TO])) =

RESULTLOOKUP ([d/Measures] = "LIST",[d/Date]="000000")

But it gives me the error "Mismatching input TO". It does not seem to acknowledge the "TO" keyword.

Any ideas?

Thanks in advance

Accepted Solutions (1)

Accepted Solutions (1)

N1kh1l
Active Contributor
0 Kudos

fffma

I am assuming your initial value of 20 is on PRICELIST member L01. Something like below should work. Just adjust as per your model and dimension name. You can change the MEMBERSET for the Date as per your planning horizon.

MEMBERSET [d/Date] = "202301” TO "202312”
MEMBERSET[d/Measures] = "LIST"
FOREACH [d/Date]
IF DATEDIFF([d/PRICELIST].[p/VALID_FROM], [d/Date], “MONTH”) >= 0 AND DATEDIFF([d/Date], [d/PRICELIST].[p/VALID_TO], “MONTH”) > 0 THEN
DATA()=RESULTLOOKUP ([d/Date]="000000")
ENDIF
ENDFOR

Hope this helps !!

Br.

Nikhil

fulmil
Explorer
0 Kudos

Hey Nikhil, this is awesome!

we tweaked it a little bit based on further requirements but works as gold.

thanks again.

Answers (1)

Answers (1)

zeenat_17
Explorer
0 Kudos

Hi,

I am facing the issue with my Weekly based Planning model.

My Version dimension has two property "Start Year" and "End_Year" of Type Text

e.g. -> FW_202338 should forecast from W38 to W43

I am trying to write the data action by scoping my date dimension to just work for 1st 2 Weeks i.e. 202338 and 202339 as follows with the help of the NEXT function, but it is giving me the following error.

The data action works fine if i provide the entire range as seen below. but I have a specific calculation that should only work for 1st 2 weeks hence I am looking to use the Next Function but unable to do so.

can you please help.