calculated measures in Excel with MDX

[Former Member]

Hi

I have a calculation view with measures and dimensions.

What I'd like to do now are calculated measrues.

I need the MDX function PERIODSTODATE. I didn't find a way to implement that in HANA Studio.

therefore I thought I could use the Excel OLAP Tools to do that.

The MDX would look something like this:

-----------------------------------------

SUM

PERIODSTODATE

(

[Date_Calendar].[Date_Calendar].[(ALL)],

[Date_Calendar].[Date_Calendar].CURRENTMEMBER

),

(

[TimeUtility].[TimeUtility].&[1],

[Measures].[BalanceLC_Change]

)

)

-------------------------------------

What this MDX does, it sums up all values for the Measure from the beginning of the Date Dimension to the current displayed Date.

This doesn't seem to work though.

Any ideas?

Thank you

Lukas

[Former Member]

Hi Lukas,

If you re-phrase your question, just describing what the reporting requirement is, I think you'll get more useful feedback.

None of the development on HANA projects involves MDX - this is generated by (some of) the front-end tools. So, the question would be how to reach your end-requirement by modeling it in HANA.

The solution should then be query language agnostic.

What you're describing sounds like time period calculations, i.e. MTD, YTD - which have been very well documented here on SCN. Please confirm though, and folks like myself can provide more feedback.

[Former Member]

Hi Jody

thank you for your advice. I'll try and rephrase my question.

I have a calculation view vith Journal Entry fact data. The measrues are debit - credit as balance.

When I open this in Excel that is fine. Adding a Time Dimension the values per period are the values for that period.

What I need is a balance value that adds up all periods up to the displayed period.

as example:

Year    Balance_change     Balance

2006     400                         400

2007     200                         600

2008     150                         750

2009     -200                        550

...

The Balance_change measure is fine, but the Balance measure I can't create so far. I'm used to do it with the mentioned MDX.

Also I'm not able to use that MDX with Excel.

Thank you.

Lukas

[Former Member]

Hi Lukas,

I don't know of any slick way of doing that within a data model off the top of my head. If you know ahead of time how many years back you want to consider, and if you know what the latest year you're interested in (i.e. via an input parameters), I could think of some clumsy ways to handle this in a HANA data model. However, I don't think that's really what you want. I'd recommend posting your question to one of the other SCN groups, especially the ones for Analysis for Office - SAP's front-end of choice for OLAP analysis in Excel. There may be a "native" way to handle your requirement on the front-end (whether in AO or another tool).

Cheers,

Jody

[Former Member]

Thank you Jody

I will look into that.

regards, Lukas