Last six months Average on actuals needs to post on Forecast .Logic not working for JAN FEB MARCH

[BalaValluru]

time-dimension-members.jpg

Hi Experts

I need a help on to calculate last six months avg on actual s needs to post on forecast up to March dynamically My financial year starts from April to March.I have logic working up to December period only but not calculating for JAN ,FEB,MARCH.

Please find my logic and time dimension members and kindly help me.

*SELECT(%M%,"[MONTHNUM]",TIME,"[ID] = %TIME_SET%")

*SELECT(%Y%,"[YEAR]",TIME,"[ID] = %TIME_SET%")

*SELECT(%ACT%,"[ID]",TIME,"[YEAR] = %Y% AND [ID]<%TIME_SET% AND [CALC] = N")

*SELECT(%FCS%,"[ID]",TIME,"[YEAR] = %Y% AND [ID]>= %TIME_SET% AND [CALC] = N")

*XDIM_MEMBERSET CATEGORY = Actual

*XDIM_MEMBERSET TIME = %ACT% *FOR %F% = %FCS%

*WHEN TIME *IS* *REC(EXPRESSION = %VALUE%/6,TIME =%F%,CATEGORY = FORECAST)

*ENDWHEN

*NEXT

my Time dimension members in attachments

[former_member186338 · Accepted solution]

The correct universal script will be:

*SELECT(%TID%,TIMEID,TIME,ID = %TIME_SET%)
*SELECT(%Y%,YEAR,TIME,ID = %TIME_SET%)
*SELECT(%PREV%,ID,TIME,TIMEID<%TID% AND YEAR=%Y% AND CALC=N) //Prev periods
*SELECT(%NEXT%,ID,TIME,TIMEID>=%TID% AND YEAR=%Y% AND CALC=N) //Next periods
*SELECT(%LTID%,TIMEID,TIME,TIMEID<%TID% AND YEAR=%Y% AND CALC=N) //Prev TID's
*SELECT(%NUM%,MONTHNUM,TIME,TIMEID>=%LTID% AND TIMEID<%TID% AND CALC=N) //Number

*XDIM_MEMBERSET TIME=%PREV%
*XDIM_MEMBERSET CATEGORY = Actual

*WHEN TIME
*IS *
*FOR %T%=%NEXT%
*REC(EXPRESSION=%VALUE%/%NUM%,TIME=%T%,CATEGORY=FORECAST)
*NEXT
*ENDWHEN

Valid %TIME_SET% - from the second period of the fiscal year!

P.S. UJKT result for %TIME_SET%=2017.05 (in my sample fiscal year=calendar year):

LGX:

*XDIM_MEMBERSET TIME=2017.01,2017.02,2017.03,2017.04
*WHEN TIME
*IS *
*REC(EXPRESSION=%VALUE%/4,TIME=2017.05)
*REC(EXPRESSION=%VALUE%/4,TIME=2017.06)
*REC(EXPRESSION=%VALUE%/4,TIME=2017.07)
*REC(EXPRESSION=%VALUE%/4,TIME=2017.08)
*REC(EXPRESSION=%VALUE%/4,TIME=2017.09)
*REC(EXPRESSION=%VALUE%/4,TIME=2017.10)
*REC(EXPRESSION=%VALUE%/4,TIME=2017.11)
*REC(EXPRESSION=%VALUE%/4,TIME=2017.12)
*ENDWHEN

[BalaValluru · Accepted solution]

Thank you very much Vadim,

I will test and come back

Thanks

Kishore V

[BalaValluru · Accepted solution]

Hi Vadim,

Thank you for help . As of now it is working up to December . Hopefully works up to march also once I change the property members in Time dimension.

Thanks a lot

Kishore V

[BalaValluru]

Hi Vadim,

User will select first Actual month and time selection based on that forecast should post

Thanks

KishoreV

[former_member186338]

Sorry, but looks meaningless!

Actual months are BEFORE Forecast months!

[former_member186338]

Sample for your fiscal year

2017.04 - Actual

2017.05 - Actual

2017.06 - Actual

2017.07 - Actual

2017.08 - Actual

2017.09 - Forecast

2017.10 - Forecast

...

2018.03 - Forecast

User select 2017.09 - first Forecast month and average of Actual 2017.04,...,2017.08 has to be written to Forecast 2017.09,...,2018.03

[BalaValluru]

Hi Vadim ,

Thank you and I will change the Period and Year properties as you suggested.

Can you please give me an insight to calculate the forecast values as shown below example

Time selection : 2015.05

Actuals values for 2015.04+2015.05/2= Forecast values up to March should post

Time selection : 2015.06

Actuals values for 2015.04+2015.05+2015.06/3= Forecast values up to March should post

( If I select time dimension 2015.05 April month and may month actuals divided by 2 and should post to forecast values for each month dynamically.)

Thanks

Kishore

[former_member186338]

Read my answer!

User will select first forecast month!

[BalaValluru]

Hi Vadim

Please find the screen shots in insert images

[former_member186338]

Still incorrect!

PERIOD property has to be fiscal month, not calendar! 2017.04 - JAN, ...

YEAR property - fiscal year! 2017.01...2017.03 - 2016, 2017.04,... - 2017

[BalaValluru]

time-mem-hierarchy.jpgtime-dimension-members.jpg

Hi Vadim

Please find the attached screen shots.

Thanks

Kishore

[former_member186338]

I have already told you that you have to use "Insert Image" instead of incorrect "Insert File"!

I will not answer questions with "Insert File"

[former_member186338]

"My financial year starts from April to March" - then your TIME dimension is configured incorrectly! Look on H1

And never use "Insert File" to attach images, use correct "Insert Image"!

P.S. And please explain your requirements once again - what do you want to enter for %TIME_SET%

[BalaValluru]

Hi Vadim,

Thank you for your reply and sorry for delay !

Yes, I agreed the Time dimension is incorrect and corrected the Hierarchy as per requirement of FY: April to March.. Please find the screen shot in attachment.

Requirement : When we enter the Time period in %TIME_SET% as below example:

Time selection : 2015.05

Actual values for 2015.04+2015.05/2= Forecast values up to March should post

Time selection : 2015.06

Actual values for 2015.04+2015.05+2015.06/3= Forecast values up to March should post

Time selection : 2015.07

Actual values for 2015.04+2015.05+2015.06+2017.07/4= Forecast values up to March should post

As per above example should work dynamically with Weighted Average for each month for all the time periods.

Please help me writing the logic

[former_member186338]

2 valluru

Screenshot not found!