Distribution of variances across remaining forecast months - possible via script logic?

[former_member201314]

Hi guys,

Within a WHEN/REC(Expression)/ENDWHEN - Statement data records being scoped in the XDIM_MEMBERSET - Statement get modfied/created and posted against the same or altered members of the dimensions, involved in the data model. OK.

Question:

Is it possible to dynamically reference (i.e read from the database) a comparison value (e.g. same members, just another Forecast Version) within a REC Statement?

The aim is to calculate a variance between two (previous and current) forecast versions. The variance should be split accross the remaining forecast months of the current forecast version. Mind you that the reference value would have to be read for each record being processed. (Variances will be different for each data record.)

As far as I see, a simple *LOOKUP won't work since it does not allow a dynamic selection of dimension members..

Any ideas?

Cheers

Claus

P.S. The EPM out-of-the-box functionality of spread distribute, etc. won't help in our particular context of user requirements. And: If possible, I would like to avoid VBA, hence my question regarding using script logic...

[former_member186338 · Accepted solution]

Easy!

*XDIM_MEMBERSET CATEGORY=FC3,FC2 //FC3 - Current forecast, FC2 - prev. forecast
*XDIM_MEMBERSET TIME=%MONTHS% //months to calculate difference - logic not defined! Ex: 2018.01...2018.09
// %REMAININGMONTHS% - remaining months, logic not defined! Ex: 2018.10,2018.11,2018.12
// %NUMREM% - number of remaining months, logic not defined! Ex: 3
*WHEN CATEGORY
*IS FC2 // "+"
*FOR %T1%=%REMAININGMONTHS%
*REC(EXPRESSION=%VALUE%/%NUMREM%,TIME=%T1%)
*NEXT
*IS FC3 // "-"
*FOR %T2%=%REMAININGMONTHS%
*REC(EXPRESSION=-%VALUE%/%NUMREM%,TIME=%T2%)
*NEXT
*ENDWHEN

No LOOKUP...

[former_member201314 · Accepted solution]

Thanks heaps, Vadim!

I will give your approach a go and will let you know about the outcome...

Cheers

Claus

[former_member201314 · Accepted solution]

Hi Vadim,

I adapted your coding approach and came up with the following logic script:

*SELECT (%YEAR%, [YEAR], VWR_PLANUNGSZYKL, [ID] = %VWR_PLANUNGSZYKL_SET%) //Example: 2018
*SELECT (%AKTM%, [AKT_MONAT], VWR_KATEGORIE, [ID] = %VWR_KATEGORIE_SET%) //Example: 01
*SELECT (%AKTMNUM%, [AKT_MONNUM], VWR_KATEGORIE, [ID] = %VWR_KATEGORIE_SET%) //Example: 1
*SELECT (%VS_ID%],[VS_ID],VWR_KATEGORIE, [ID] = %VWR_KATEGORIE_SET%) // Example: VS0111
*SELECT (%REMMON%, [ID], TIME, [%VS_ID%] = %YEAR%) //Example: 2018.02, 2018.03, ... 2018.12

*XDIM_MEMBERSET VWR_BAUTEIL = <ALL>
*XDIM_MEMBERSET VWR_PSP = <ALL>
*XDIM_MEMBERSET VWR_P_DATENQUELL = <ALL>
*XDIM_MEMBERSET VWR_P_KOSTENART = <ALL>
*XDIM_MEMBERSET VWR_P_KOSTENSTEL = <ALL>
*XDIM_MEMBERSET VWR_PLANUNGSZYKL = %VWR_PLANUNGSZYKL_SET%
*XDIM_MEMBERSET VWR_KATEGORIE = %VWR_KATEGORIE_SET%,VS.00_12 //%VWR_KATEGORIE_SET% - CURRENT forecast, VS.00_12 - prev. forecast
*XDIM_MEMBERSET TIME = %YEAR%.%AKTM% //months to calculate difference. Here: Just the actual month 2018.01
*XDIM_MEMBERSET MEASURES = "PERIODIC"

*WHEN VWR_KATEGORIE
*IS VS.00_12 // "+" = previous FC
*FOR %T1% = %REMMON%
*REC(EXPRESSION = %VALUE%/(12-%AKTMNUM%)),TIME = %T1%,VWR_KATEGORIE = %VWR_KATEGORIE_SET%) //splits the VALUE of 2018.01 and previous FC and spreads it equally across the remaining months of CURRENT FC
*NEXT
*IS %VWR_KATEGORIE_SET% // "-" = current FC
*FOR %T2% = %REMMON%
*REC(EXPRESSION = -%VALUE%/(12-%AKTMNUM%)),TIME = %T2%) //splits the negative VALUE of 2018.01 and CURRENT FC and spreads it equally across the remaining months of CURRENT FC
*NEXT
*ENDWHEN

------

However, although the differences get posted correctly (-100 € in each month), the original values (2000 € in each month 2018.02 ff) get lost.

--> before logic execution:

--> after logic execution:

How can I post the -100 on top of the 2000?

Unfortunately, the command *REC(EXPRESSION = %VALUE%+(-%VALUE%/(12-%AKTMNUM%)),TIME = %T2%) won't work since this command only considers XDIM_MEMBERSET TIME 2018.01. That would end up with +3000 in each of the months 2018.02, ff...

Cheers

Claus

[former_member186338 · Accepted solution]

Use different audittrail for target. The existing value will not be affected.

Then you you can sum existing value with this special audittrail.