Sap analytics cloud - Derive date from version in data action script

[Guillaume_P]

Hi all.

In a data action script I need to select both the version AND the date to set the scope of data I'll work with.

My versions IDs looks like as below :

2023_B0

2023_B1

2023_B2

2023_B3

2024_B0

...

As to say :

- 4 differents budgets per year ("B" in the ID means Budget, the last digit is the "version" of the version)

- the budget ID already contains the related year (with the 4 firsts digits, and using a property (type text)

I'm unsuccessfully trying to find a clever trick not to force my users to fill both version and date prompts.

When I try to use the year property of my version I get an "incompatibility type" error :

[d/Date] = NEXT(@i, "YEAR", [d/Version].[p/ANNEE])

("ANNEE" is "YEAR" in french)

While the line below is working fine :

[d/Date] = NEXT(@i, "YEAR", %date_dec%)

(because %date_dec% is a variable typed as a date)

Available types for properties are text, integer, decimal.

Any idea ?

Is there a way to cast a variable type (maybe text) to "date" ?

Thanks in advance & regards.

Guillaume P.

[N1kh1l]

guillaumep

What is the type of @I variable, Integer ?

Your syntax looks ok to me. I tried to re create and it validates just fine

Start Date is attribute of type Text

Advanced Formula

If you are trying to use it in MEMERSET then you need to assign a TO part also

INTEGER @I
MEMBERSET [d/CALMONTH]=NEXT(@I,"YEAR",[d/Version].[p/START_DATE]) TO NEXT(@I,"YEAR",[d/Version].[p/START_DATE])

Hope this helps !!

Nikhil

[William_Yu · Accepted solution]

You may write your memberset as following:

MEMBERSET [d/Date] = [d/Version].[p/ANNEE] TO [d/Version].[p/ANNEE]

[Guillaume_P · Accepted solution]

Hello William, thanks for answering.

I didn't perform lots of tests but so far it's working fine.

Is there a way to achieve the same behaviour in a table object (in a story) please ?

(as to say driving both version and date through only one version input control)

(the YEAR property of the version should be the first column, and next column should be "YEAR property + 1 year)

In green this is "direct selection", in orange it should be through the property

Regards.

Guillaume P.

[N1kh1l · Accepted solution]

guillaumep

For the story part, currently no way to use attributes of one dimension to drive another dimension ( something similar to EPMOverirde of BPC). But you can use scripting to get the same behavior. From the version input control read the selected key, read its properties and then use that to pass it to setdimensionfilter

Helpful API's

https://help.sap.com/doc/958d4c11261f42e992e8d01a4c0dde25/release/en-US/index.html#InputControlDataS...

https://help.sap.com/doc/958d4c11261f42e992e8d01a4c0dde25/release/en-US/index.html#CommentingDataSou...

Nikhil

[William_Yu · Accepted solution]

One option might be to use the dynamic forecast layout with FIND formula, check this help page

https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/3e2ad3b322494a4cad6b1...

[Guillaume_P]

Thanks a lot for this seful and fast feedbach william.yu .

Thanks you also nikhil_1486

(yes, my variable is an integer)

Rgds.

Guillaume P.