In SAC, how to redistribute the period totals to all of its' weeks equally using a data action?

[skcpaleti65]

Hi Experts, 

In the story screenshot below, I am using the below data action code to get the period total redistributed to all of its' weeks equally, either to 4 or 5 of them, based on the 4-4-5 pattern, for a range of periods. But, it's not working. What am I doing wrong?  Note: start period is a parameter and 2024 period 1 is selected: "202401"

Thanks in advance.

CONFIG.TIME_HIERARCHY = FISCALYEAR

CONFIG.GENERATE_UNBOOKED_DATA = ON

CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE = OFF

// Start Period is selected as a parameter

MEMBERSET [d/Date] = %PERIOD% TO NEXT(17, "PERIOD", TODAY())  

MEMBERSET [d/Measures] = "Amount"

VARIABLEMEMBER #PERTOTAL OF [d/Date]

INTEGER @Count

DATA([d/Date] = #PERTOTAL) = RESULTLOOKUP()

IF [d/Date].[p/PATTERN_FISC_PERIODDESC] = ("P03", "P06", "P09", "P12") THEN

FOR @Count = 0 TO 4

DATA([d/Date] = NEXT(@Count, "WEEK", [d/Date])) = RESULTLOOKUP([d/Date] = #PERTOTAL) / 5

ENDFOR

ELSE

FOR @Count = 0 TO 3

DATA([d/Date] = NEXT(@Count, "WEEK", [d/Date])) = RESULTLOOKUP([d/Date] = #PERTOTAL) / 4

ENDFOR

ENDIF

The expected result once executed the data action should be:

Thanks & Best wishes,

SP

[William_Yu]

Hi skcpaleti65, 

  Re -calculate average of given periods could be simply written with below code.  If your requirement is to calculate for multiple periods by one shot, I would recommend to call the data action one period by one period via few JS code in story. 

Best regards, William 

[skcpaleti65]

Thanks William_Yu. Yes, we need to do this for multiple periods (18 periods) at one shot. What is the Memberset of the Date that you have set in the beginning of the code? I really appreciate it. Can you please write the blog on this or white paper on this. Dates are complex things to handle. But with one example showing with the Jave Scripts and calling 'data action' as parameter, will open up the possibilities for the community to explore more. Thanks.

[William_Yu]

The memberset of the date dimension is just a parameter can be passed in, it can be period, quarter or year. Sorry I don't have for any script example or blog, but it should be quite straight forward if anyone has any JS experience.

[skcpaleti65]

William_Yu, if we pass the year in the parameter, then the solution would work for the entire year, period by period at once, correct? If so, then why do we need call the data action one period by one period. Thanks

[skcpaleti65]

Hi William_Yu, even if I enter data for one week of a period, it is dividing it equally for all the 104 weeks (2 years) + '000000' week. How to fix this to work for respective periods only. Thanks.

[William_Yu]

The sample code(simple and performing) I provided calculates average for ALL weeks specified memberset . That's why I suggest to you all the data action from story period by period with scripting.

[skcpaleti65]

Will try that, William_Yu. Once done that, I will mark this as the solution. Thanks.