This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,747 Users
  • 2,269,776 Discussions
  • 7,916,827 Comments

Discussions

ASO Member formula for calculating Average

ksdesh
ksdesh Member Posts: 103 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: 103 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.