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
0 Kudos

@mayiya 

This could have been done in a simpler way but just studying your code, once you have the avg calculated and posted in September, you can just copy it to next 3 months right ?

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

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

ENDFOR

 

Nikhil

mayiya
Participant
0 Kudos

Hi Nikhil. Not really, my issue is with accumulative values and I have to do this on a lot of calculations.   Suppose this scenario (without avg, only the sum):

expected values2.png

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

@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")    // Result for first month of projection

FOR @i = 1 TO @Iterate STEP 1

              @x = @i - 1

              @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)

ENDFOR

 

But the result is : 

result2.png

Please let me know if there is another easiest way to do this accumulative values. 

Thanks. 

N1kh1l
Active Contributor
0 Kudos
Ok. But your initial post shows expected value as 541.5 in all 4 months under forecast. You want the average to be updated based on values you post each month from sep to dec?
mayiya
Participant
0 Kudos

That´s right Nikhil.

September = (SUM January-August)/ 8

October=  (SUM January-September)/9

November=  (SUM January-October)/10

December= (SUM January - November)/11