EPMDIM_CURRENT_MEMBER

[Former Member]

Hi Experts,

I am trying to write the formula for a local member to compare the current member with its previous member. Is there a keyword such as EMPDIM_CURRENT_MEMBER to refer to the previous member?

If not, any idea in how to do so?

Thanks.

J.I.

[former_member186338 · Accepted solution]

Hi,

EMPDIM_CURRENT_MEMBER(SOME_DIM) returns the cell reference with current member of the dimension SOME_DIM - for row or column axis. There is no formula to get a reference of previous member, but you can use the following:

For member in row axis:

Assuming that previous member is located in previous row:

=INDIRECT(ADDRESS(ROW(EMPDIM_CURRENT_MEMBER(SOME_DIM))-1,COLUMN(EMPDIM_CURRENT_MEMBER(SOME_DIM))))

Vadim

P.S. In case of fixed columns you can simply reference the cell located in the previous row of the first member in row axis column.

[Former Member · Accepted solution]

Hi Vadim,

I have fixed columns but dynamic rows, in this case how can I reference to the cell of the first row of the query?

[former_member186338 · Accepted solution]

You can use formula in my prev post - it's universal.

Or, for example:

First data row of the report is 9 (the row with first row axis member)

Row axis column is A

Then reference $A8 in the local member formula - A will be constant and 8 will be adjusted according to the current row.

Vadim

[Former Member · Accepted solution]

When I try a cell reference in a local member formula, it doesn't adjust it to the current row.

If I write A8 in the formula and the report starts in row 50 (dynamic), the first row in the report will reference to A8 and the following to A9, and so on.

By the way, the INDIRECT formula doesn't work for me; it returns #NAME?

It Works now, the formula was EMP!!

[former_member186338 · Accepted solution]

Ups, you have dynamic report start! Then - only INDIRECT!

#NAME is because you made a mistake: not EMPDIM_CURRENT_MEMBER but EPMDIM_CURRENT_MEMBER and I copied it into formula

Vadim

[Former Member · Accepted solution]

Hi again,

I'm having problems when mixing the indirect formula with other EPM functions.

When trying:

=INDIRECT(ADDRESS(ROW(EPMDIM_CURRENT_MEMBER(SOME_DIM))-1,COLUMN(EPMDIM_CURRENT_MEMBER(SOME_DIM))))

It Works perfectly, returning the name of the previous member.

But if I try:

=EPMMemberID(INDIRECT(ADDRESS(ROW(EPMDIM_CURRENT_MEMBER(SOME_DIM))-1,COLUMN(EPMDIM_CURRENT_MEMBER(SOME_DIM)))))

The INDIRECT formula won't work, returning the cell reference, and then EPMMemberID doesn't work.

Is it possible that INDIRECT only Works when being at the beginning of the formula??

[former_member186338 · Accepted solution]

What for do you want to use EPMMemberID in this formula? This function is used in some other formulas...

For example the following works fine:

=EPMMemberDesc(EPMMemberID(INDIRECT(ADDRESS(ROW(EPMDIM_CURRENT_MEMBER(INACCT))-1,COLUMN(EPMDIM_CURRENT_MEMBER(INACCT))))))

For the cell's that doesn't contain valid EPMMemberO function the result will be #NoData, for correct cells - the description.

Vadim