0

im new to Hyperion and i have a Problem with some Data.

I do not want to cumulatively calculate data instead I would like to use the differences of the two consecutive values to make evaluations.

Example:

Start:           100
                 200
                 300

The result should be 200 and not 600 is this possible? And if yes how?

Thanks!

My Hyperion Version: 11.1.2.0000

Carsten I
  • 33
  • 10

1 Answers1

0

It sounds like this is what you're looking for:

Value  Difference   strDifference     Output   strOutput
100    0            0                 0        0
200    100          200-100 = 100     100      0+100 = 100
300    100          300-200 = 100     200      100+100 = 200
150    -150         150-300 = -150    50       200+(-150) = 50

So, the formula for the column Difference is:

if(Prior(Difference)==null) {0} 
else {Value-Prior(Value)}

And, the formula for the column Output is:

if(Prior(Difference)==null) {0} 
else {Prior(Difference)+Difference}

Unless you want the total to be the Output in which case it's more simple:

Sum(Difference)

Sort order matters, obviously.

This is highly inefficient; if your dataset is large, Hyperion will take a long time to process the section, if it finishes at all.

undrline - Reinstate Monica
  • 526
  • 1
  • 7
  • 18
  • Why wouldn't the formula for `Output` be `Cume(Difference)`? – dougp Sep 06 '19 at 20:26
  • @dougp It can be. I don't have access to Hyperion anymore, and don't think I did at the time I answered. So, I'm guessing I did it that way because I couldn't remember/test how Hyperion would behave with nulls and blanks. Or possibly, I was just trying to be clear: Prior shows exactly what I was trying to achieve in interpreting what it is they want. Cume is sure to be much more efficient, but they might not understand it as well. – undrline - Reinstate Monica Sep 12 '19 at 17:03