cancel
Showing results for 
Search instead for 
Did you mean: 

Date Dimension in SAC

Vasu_Sac
Explorer
0 Kudos

Hi 

I have story filter FY Period hierarchy selection and Date dimension also maintain Fiscal year period hierarchy as default hierarchy. My Date dimension data as below (Calendar year Month is Key value).

Date Dimension DataDate Dimension DataFiscal Year Period Hierarchy SettingFiscal Year Period Hierarchy Setting

FYMonth_Input Variable.png

I am calculating simple multiplication of two KFs and saving to another KF through advance formula.  I have created FYMonth_Input parameter with settings (Hierarchy: fiscal Year period, Level: Any). 

MEMBERSET [d/CompCode] = (%CCODE_Input%)
MEMBERSET [d/SalesGroup] = (%SGroup_Input%)
MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/FYP], %FYMonth_Input%))

DATA([d/Measures] = "Sales_Output") = RESULTLOOKUP([d/Measures] = "Sales_Input", [d/CompCode] = %CCODE_Input%, [d/SalesGroup] = %SGroup_Input%) * RESULTLOOKUP([d/Measures] = "Sales_Forecast", [d/CompCode] = %CCODE_Input%, [d/SalesGroup] = %SGroup_Input%)

The advance formula working and providing multiplication of two KFs. But I would like to add Date parameter as well in RESULTLOOKUP, but it is not allowing me to add Date parameter.

The story filter will pass the Date parameter value, hence I would like to include Date parameter as well in RESULTLOOKUP to get the KF values for same fiscal year period.

I don't know how it works, The RESULTLOOKUP will consider the MEMBERSET [d/Date] parameter input or not. If not consider KF values will aggregate other months which is not correct. 

And If I change FY Period Parameter settings  level to Leaf (Hierarchy: fiscal Year period, Level: Leaf), then it allowing to add Date parameter in RESULTLOOKUP, This time MEMBERSET [d/Date] is just reading from parameter not from hierarchy structure (not like above code).

MEMBERSET [d/CompCode] = (%CCODE_Input%)
MEMBERSET [d/SalesGroup] = (%SGroup_Input%)
MEMBERSET [d/Date] = (%FYMonth_Input%)
DATA([d/Measures] = "Sales_Output") = RESULTLOOKUP([d/Measures] = "Sales_Input", [d/CompCode] = %CCODE_Input%, [d/SalesGroup] = %SGroup_Input%, [d/Date] = (%FYMonth_Input%)) * RESULTLOOKUP([d/Measures] = "Sales_Forecast", [d/CompCode] = %CCODE_Input%, [d/SalesGroup] = %SGroup_Input%, [d/Date] = (%FYMonth_Input%))

There is no errors in code but it is not providing any result.

Please explain me, why this code is not working even it allowed Date parameter in RESULTLOOKUP.

And the first code is working without allowing Date parameter in RESULTLOOKUP but my worry is may aggregate the KF values to other months.

Regards,

Vasu

 

Hi @N1kh1l 

Thanks for the clarification.

I changed to parameter value to leaf and included Date parameter in RESULTLOOKUP but as I said it’s not provided any value.

MEMBERSET [d/CompCode] = (%CCODE_Input%)
MEMBERSET [d/SalesGroup] = (%SGroup_Input%)
MEMBERSET [d/Date] = (%FYMonth_Input%)
DATA([d/Measures] = "Sales_Output") = RESULTLOOKUP([d/Measures] = "Sales_Input", [d/CompCode] = %CCODE_Input%, [d/SalesGroup] = %SGroup_Input%, [d/Date] = (%FYMonth_Input%)) * RESULTLOOKUP([d/Measures] = "Sales_Forecast", [d/CompCode] = %CCODE_Input%, [d/SalesGroup] = %SGroup_Input%, [d/Date] = (%FYMonth_Input%))

I assuming this is due to FYMonth  to CalYmonth  internal conversion is not happening .when I configure Date parameter is Fiscal year period.

In my case if I select Apr 24 (FY25), the FYM value is 202501 passing. Model level data was stored in 202404 because my Date dimension key Calendar year Month.

Logically CalYM 202404 is same as FYM 202501 at Model level through Date Dim.

But it seems the values are not passing or conversion due to ‘LEAF’ setting at parameter.

Not clear why it is not working.  

Regards,

Vasu

View Entire Topic
N1kh1l
Active Contributor
0 Kudos

@Vasu_Sac 

Based on what hierarchy you want the date dimension being treated in Data Action you can add the below CONFIG statement in DA before the MEMBERSET s

If your default hierarchy is FYQP and you want DA to treat date as calendar year 

CONFIG.TIME_HIERARCHY = CALENDARYEAR

 

Br.

Nikhil