Average Value with empty cells

[ChristianSass]

Hello and Hello Vadim,

i've tried to use "AVG" formula to calculate the average of an account node. As long as the period has values the formula works great but as soon as there are "empty cells" the result is not correct. Please see attached picture ... Is there any solution on that? I've tried to insert dummy zero value to fill up the empty cells but this can't be the soluton

AVG(PERIODSTODATE([TIME].[LEVEL00],[TIME].CURRENTMEMBER),[ACCOUNT].[PARENTH1].[BPCN45])

[former_member186338 · Accepted solution]

Use

CoalesceEmpty

CoalesceEmpty (MDX)

[former_member186338 · Accepted solution]

P.S. Look on my answer here

[ChristianSass · Accepted solution]

Hi,

I can't find any usefull example on that in the Internet. How do I use this?

I've tried:

AVG(PERIODSTODATE([TIME].[LEVEL00],[TIME].CURRENTMEMBER),CoalesceEmpty([ACCOUNT].[PARENTH1].[BPCN45],0))

and it works ...

[former_member186338 · Accepted solution]

In my link my sample was:

AVG(PERIODSTODATE([TIME].[LEVEL00], [TIME].CURRENTMEMBER),COALESCEEMPTY([ACCOUNT_HR].[HC_HR_FTE],0))

[ChristianSass · Accepted solution]

I've not seen your 2nd post .... Great help. Thx

[ChristianSass · Accepted solution]

last question on this

With the above formula I don't get the average on year Level. I've tried it with different iff functions but it's not what I need

[former_member186338 · Accepted solution]

I don't know "what you need"

but:

IIF([PERIODS].CURRENTMEMBER.PROPERTIES("LEVEL")="YEAR",SUM(DESCENDANTS([PERIODS].CURRENTMEMBER,[PERIODS].[LEVEL02],LEAVES),[S04001601])/12,AVG(PERIODSTODATE([PERIODS].[LEVEL00],[PERIODS].CURRENTMEMBER),CoalesceEmpty([S04001601],0)));SOLVE_ORDER=10

or...

[ChristianSass · Accepted solution]

Hi,

I would like to see the last average value of period XXXX.12 in XXXX.TOTAL column as well. I've played around with .LAG(-11) but without success. I will try your formula tomorrow.

Another tiny question. Do you recommend nested formulas? I don't have "hana mdx" at the moment due to back-end issues but we'll get it soon and then I would like to use nested formulas. But the last time I had issues with them as well

Thank you

[former_member186338 · Accepted solution]

"I would like to see the last average value of period XXXX.12 in XXXX.TOTAL" - my formula will do it.

LAG is not relevant...

"Do you recommend nested formulas?" - never used, no idea

[former_member186338 · Accepted solution]

Even more elegant formula

AVG(PERIODSTODATE([PERIODS].[LEVEL00],CLOSINGPERIOD([PERIODS].[LEVEL02],[PERIODS].CURRENTMEMBER)),CoalesceEmpty([S04001601],0));SOLVE_ORDER=10

Will correctly show values for QUARTER and YEAR levels..

[ChristianSass · Accepted solution]

Big Thank You!

Have you tried to create an own MEASURES for AVG? It would be great to have YTD, QTD, Periodic, and AVG

[former_member186338 · Accepted solution]

AWG measure can be created, but it's useless if you need only some accounts...

Anyway, it depends on the reporting requirements!

[ChristianSass · Accepted solution]

Sorry Vadim but I've one question after the other ...

Is it possible to create a formual that works differently in Periodic or YTD measures selection?

The above formula is used on an AST account type and I've the correct result on Periodic view. Now I would like to Switch to YTD and the result should cumulate the single period values

[former_member186338 · Accepted solution]

Sorry, but AST accounts are always YTD

If you change the measure to YTD you will have the same result as for PERIODIC!

[ChristianSass · Accepted solution]

I know ... we apply a percentage on the AST which is an expense that we would like to see per period and cumulated

[former_member186338 · Accepted solution]

Looks like you have some misunderstanding on the sequence of events...

First MEASURE will calculate values for real dimension members.

Then dimension measure formula will use values calculated by measures...

[ChristianSass · Accepted solution]

Hi Vadim,

I've to come back on this post.

CoalesceEmpty was a great help but it is not supported with HANA MDX!

Now I've tried something like this:

Avg(PeriodsToDate([TIME].[LEVEL 00],ClosingPeriod([TIME].[LEVEL 02],[TIME].CurrentMember)),IIF([NODE123]=0,0,[NODE123]))

the formula is excepted by WebUI but I get a dump in EXCEL

Do you have any other idea to get the right avg?

Many thanks!

[former_member186338 · Accepted solution]

"CoalesceEmpty was a great help but it is not supported with HANA MDX" - are you 100% sure?

It's the only correct approach!

[ChristianSass · Accepted solution]

We've an answer from SAP that it is not Support ! from this morning

[former_member186338 · Accepted solution]

Unbelievable! It's a bug!

[former_member186338 · Accepted solution]

It means that the AVG function is useless!

[ChristianSass · Accepted solution]

This is causing us big Problems

If tried to do it with a nested formula

1ST_FORMULA: IIF([NODE]=0,0,[NODE])

2ND_FORMULA:

Avg(PeriodsToDate([TIME].[LEVEL 00],ClosingPeriod([TIME].[LEVEL 02],[TIME].CurrentMember)),[1ST_FORMULA])

Result: 2nd Formula accumulateds values ....

now, I could divide by month num or?

[former_member186338 · Accepted solution]

you can use SUM and divide by number of periods...

[ChristianSass · Accepted solution]

I'm looking for the post where you showed me how to use the MONTHNUM property from TIME Dimension

[former_member186338 · Accepted solution]

Not MONTHNUM but BASE_PERIOD

[ChristianSass · Accepted solution]

it works with

[TIME].CurrentMember.Properties("MONTHNUM")

[ChristianSass · Accepted solution]

hi Vadim,

as always! you are right ....

MONTHNUM has a wrong result on YEAR Level. BASE_PERIOD is much better

[former_member186338 · Accepted solution]

BASE_PERIOD is numeric and MONTHNUM is string