SAC: Data Action Advanced Formulas - MEMBERSET "from TO" for Date dimension

[former_member40251]

Hello Experts!

I need to create an advanced formula in a Data Action, where in MEMBERSET I need to use a "FROM - TO" for the planning date dimension, where "from" is the calendar month from "Hiring date" dimension, and "TO" must be the last month of the year (from the planning Date dimension).

I'm trying to use this:

MEMBERSET [d/Date]= [d/Hiring_Date].[p/CALMONTH] TO LAST([d/Date])

where [d/Date] is the planning date dimension (Month/Year) and [d/hiring_date] is a secondary date dimension (day/month/year).

Problem: The system is not allowing me to use "LAST" function

second option

MEMBERSET [d/Date]= [d/Hiring_Date].[p/CALMONTH] to %parameter%

Problem: system not allowing me to use "TO" + variable when "from is a parameter from "Hiring Date"

the third option worked, but is not the best solution as I'd like to automatically derive the month from the hiring date and the last month of the planning date year.

MEMBERSET [d/Mes_Ano]= %hiring_month% to %last_month%

Does anyone have a better solution to share?

kind regards

Mayumi

[former_member40251]

Thanks William!

It worked with your suggestion.

It would be perfect if instead of a variable for the starting period in "Memberset", I could use the "CalendarMonth" property from the Hiring Date (i tried but not working, SAC considers all the months), so the user does not have to fill both "starting month" and "Hiring Date". But not to fill the ending month already made it better than before.

Kind regards

Mayumi

[William_Yu1]

Hi may24071978_,

Yeah, you can't use LAST() keyword in MEMERSET statement as described in help document.

May be there is a workaround as below you can try out:

• Define only starrting period in MEMBERSET(via parameter)
• Count number of periods between starting period and last period
• Loop with FOR statement for calculation logic

Best regards, William