cancel
Showing results for 
Search instead for 
Did you mean: 

SAC Advanced Formulas Accumulated Values

mayiya
Participant
0 Kudos

Hello experts. 

I need to calculate the average like = accumulative values from January to previous month of month to project/ Number of previous month.  Example: If my month of projection is September then accumulative values will be from January to August/ 8. 

The expected values are as follows:

expected values.png

I have the following code on Advanced formula:

MEMBERSET [d/Measures] = "VALOR"

MEMBERSET [d/Date] = BASEMEMBER([d/Date].[h/YQM], %01AnioProy%)

VARIABLEMEMBER #TotalAnioReal OF [d/Date]

INTEGER @i = 1

INTEGER @x = 0

INTEGER @Month

INTEGER @Iterate

INTEGER @AcumProy

INTEGER @NumMonth

@Month = MONTH(%02InicioProy%)  // Parameter used for initial month of projection. In this example es 202309

@Iterate = 12 - @Month

@NumMonth = @Month - 1

DELETE([d/CONCEPT] = "Prom_TX_Q")

DATA([d/Date] = #TotalAnioReal, [d/CONCEPT] = "Prom_TX_Q") = RESULTLOOKUP([CONCEPT] = "Prom_TX_Q", [d/Version] = "public.Actual")    // Total of Actuals, for the example is from 01-08 = 4332.

@AcumProy = RESULTLOOKUP([d/CONCEPT] = "Prom_TX_Q", [d/Date] = #TotalAnioReal)

DATA([d/CONCEPT] = "Prom_TX_Q", [d/Date] = %02InicioProy%) = RESULTLOOKUP([d/Date] = #TotalAnioReal, [d/CONCEPT] = "Prom_TX_Q") / @NumMonth     // Result for first month of projection

FOR @i = 1 TO @Iterate STEP 1

              @x = @i - 1

              @NumMonth = @Month + @x

              @AcumProy = @AcumProy  + RESULTLOOKUP([d/CONCEPT] = "Prom_TX_Q", [d/Date] = NEXT(@x, "MONTH", %02InicioProy%))

              DATA([d/CONCEPT] = "Prom_TX_Q", [d/Date] = NEXT(@i, "MONTH", %02InicioProy%)) = (@AcumProy) / @NumMes

ENDFOR

 

Above code is at lowest level of granularity and is only working for i=1, when i=2 the @AcumProy is 0 so the result is wrong. 

result.png

 

Please can you help.  I'm stuck on this and have done a lot of unsuccessful test. 

 

 

View Entire Topic
N1kh1l
Active Contributor

@mayiya 

If you want to do this for multiple records you cannot use variable to accumulate as variable values are not cleared when next record arrives and it will keep adding up. Calculations will happen at lowest level only unless you aggregate explicitly. Use the below code it should work

MEMBERSET [d/Measures] = "VALOR"

MEMBERSET [d/Date] = BASEMEMBER([d/Date].[h/YQM], %01AnioProy%)
VARIABLEMEMBER #TotalAnioReal OF [d/Date]


INTEGER @i
INTEGER @x
INTEGER @Month
INTEGER @Iterate
INTEGER @AcumProy
INTEGER @NumMonth
@Month = MONTH(%02InicioProy%) // Parameter used for initial month of projection. In this example es 202309
@Iterate = 12 - @Month
@NumMonth = @Month - 1

DATA([d/Date] = #TotalAnioReal) = RESULTLOOKUP([d/Version] = "public.Actual")
DATA([d/Date] = %02InicioProy%) = RESULTLOOKUP([d/Date] = #TotalAnioReal) / @NumMonth
FOR @i = 1 TO @Iterate
@x=@i-1
DATA([d/Date] = #TotalAnioReal) = RESULTLOOKUP([d/Date] = #TotalAnioReal) + RESULTLOOKUP([d/Date] = NEXT(@x, "MONTH", %02InicioProy%))
DATA([d/Date] = NEXT(@i, "MONTH", %02InicioProy%)) = RESULTLOOKUP([d/Date] = #TotalAnioReal)/(@Month+@x)

ENDFOR

I did run a test and it gave me below results

N1kh1l_0-1708625983098.png

But I still feel you can copy the value of September  into Oct to Dec, it will give you same results

 

Br.

Nikhil

 

mayiya
Participant
0 Kudos
Nikhil you're a genius. Thanks for sharing your knowledge!! It works !!