Epmmemberoffset issue with TIME Dimension PARENTH2

[Former Member]

Hi

I have a second hierarchy in the Time dimension to calculate a rolling total for forecast time base members which may be different from the calendar time totals, example PARENTH2 total for current forecast total can be 2014.FEB to 2014.FEB.

My current forecast total is for months 2014.JAN to 2014.DEC, I'm using ="epmmemberoffset(,2014.DEC,1)" and expect to get 2015.JAN in the cell but I'm getting 2014.JAN

Any ideas on what the issue is?

Thanks

William

[former_member186338 · Accepted solution]

Hi William,

Please show screenshot of your TIME dimension to understand the issue.

By the way, it's possible to use some tricky Excel formula to simulate EPMMemberOffset:

Let

A1 contain 2014.NOV

A2 contain  2014.DEC

We want to emulate EPMMemberOffset(,A1,1)

Put this formula in B1 and B2 (copy from B1):

=IF(RIGHT(A1;3)="DEC";LEFT(A1;4)+1&".JAN";LEFT(A1;4)&"." & CHOOSE((FIND(RIGHT(A1;3);"JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC")-1)/3+1+1;"JAN";"FEB";"MAR";"APR";"MAY";"JUN";"JUL";"AUG";"SEP";"OCT";"NOV";"DEC"))

The result will be:

B1: 2014.DEC

B2: 2015.JAN

B.R. Vadim

Message was edited by: Vadim Kalinin - simplify formula! +1 - is a positive shift

[Former Member · Accepted solution]

Hi Vadim,

Thanks for the reply, always nice to hear from you. see below for Time dimension and the epmmemberoffset issue.

[former_member186338 · Accepted solution]

Please, show how you maintain TIMEID property of TIME dimension...

Vadim

[Former Member · Accepted solution]

Please see attached for Time Dimension

Anyone experiencing the same issue?

Thanks

William

[former_member186338 · Accepted solution]

And what about 2015? Do you have proper members for 2015?

[Former Member · Accepted solution]

Yes,see attached

[former_member186338 · Accepted solution]

P.S. By the way, looking closely on this picture:

I can see D1 2014.12 right formatted like a number... Do you simply type 2014.12 in the cell actually having a number? Format D1 as text, then type 2014.12 then test formula....

Vadim

[Former Member · Accepted solution]

Yes, I typed 2014.12 in D1.

Tested the following scenarios

1. When I type 2015.12 (not used in PARENTH2) in D1, E1 shows the correct time of 2016.01.

2. When I remove the 2014 base members from PARENTH2,then it works ok, E1 show 2015.01

[former_member186338 · Accepted solution]

Also test this formula with explicit member:

=EPMMemberOffset(,"[TIME].[PARENTH1].["&D1&"]",1)

And for numeric months the Excel formula will be simple:

=IF(RIGHT(D1,2)="12",LEFT(D1,4)+1&"."&RIGHT("0"&(0+1),2),LEFT(D1,4)&"."&RIGHT("0"&(RIGHT(D1,2)*1+1),2))

Vadim

Message was edited by: Vadim Kalinin - Correction in Excel formula: +1 can be replaced by +2,+3...

[former_member186338 · Accepted solution]

P.S. for sure this Excel formula is limited to +1..+12 but may be it's enough.

[Former Member · Accepted solution]

Thanks.  Has anyone else encountered this issue before?. strange issue, not sure if its a product bug...

[former_member186338 · Accepted solution]

Ups, incorrect Excel formula:

The correct formula will be:

=(LEFT($D$1,4)*1+INT((RIGHT($D$1,2)+D2-1)/12))&"."&RIGHT("0"&MOD(RIGHT($D$1,2)*1+D2-1,12)+1,2)

$D$1 - member like 2013.05

D2 - shift - positive or negative!

Vadim

[former_member186338 · Accepted solution]

Have you tested the formula with FULL member id like:

=EPMMemberOffset(,"[TIME].[PARENTH1].["&D1&"]",1)

In this formula PARENTH1 is fixed...

And my final Excel formula also produces a correct member id for any integer shift value (positive, negative or zero).

Vadim

[Former Member · Accepted solution]

Thanks Vadim. I have tested with fixing the PARENTH1 and its working. Thanks you.