Forum Stats

  • 3,824,874 Users
  • 2,260,435 Discussions
  • 7,896,336 Comments

Discussions

ASO Member formula for calculating Average

ksdesh
ksdesh Member Posts: 99 Blue Ribbon
edited Jun 15, 2022 7:03PM in Essbase On-Premise

I am trying to write a very simple member formula, but keep getting syntax errors when I verify it.

What I need is average daily revenue for the past 3 months. it does not have to be exact, just sum of past 3 months divided by 90 is good enough.

I have rolling 3 month members in my period dimension called 3M (PerXX) where XX is the period (01 to 12). I am trying to get it to work for just one month for now, will add other months once this works. I have tried the formula below:

CASE

WHEN Contains([Period].CurrentMember, {[Per03]})

THEN (CrossJoin({[A4000]},{[3M (Per03)]}) / 90)

END

This is giving the following error:

Error(1260052) - Syntax error in input MDX query on line 3 at token '/'


I have tried without the CrossJoin. the Syntax verifies but the values don't show up.

What am I doing wrong?

Tagged:

Answers

  • ksdesh
    ksdesh Member Posts: 99 Blue Ribbon

    ok, so going through the documentation I realized that Crossjoin is just giving the intersection. So it is not going to actually give the number stored in that intersection. So I modified the formula to the following:

    CASE

    WHEN Contains([Period].CurrentMember, {[Per03]})

    THEN( Sum(CrossJoin({[A4000]},{[3 MTD (Per03)]})))

    END


    But it is not giving me any data in the member.