The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

CCI01's avatar
CCI01
New Contributor
3 years ago
Solved

Building a CV with a trailing 5 quarter view

I'm working on a cube view that displays reporting data in a quarterly trend. The end user would select which quarter they want displayed and the CV would display that quarter along with the 4 prev...
Cube View
  • PeterFu's avatar
    PeterFu
    3 years ago

    Hi,

     

    You can use the syntax in the print screen below. You select the quarter you want from the parameter that gets prompted. Then you will get the four prior quarters. If you use the Member Filter Builder in the Cube View, then you will find a lot of examples of dynamic references.

     

     

     

    Peter

     

CCI01's avatar
CCI01
New Contributor
3 years ago

Hey,

 

Following up on this. How would you write a formula to take the average of the current quarter selected by the user + the prior 4 quarters?

 

Thanks.

PeterFu's avatar
PeterFu
Contributor II
3 years ago

Hi,

 

For that you do not need to create any formulas. See print screen below. You can use the members in the View dimension for what you are looking for. You have both 2 - 12 months trailing average and total in the View dimension.

 

 

Peter

  • CCI01's avatar
    CCI01
    New Contributor
    3 years ago

    Hi Peter,

     

    I took a look at the base members under the view dimension and unfortunately they are not going to work the way I am going to need them to. 

    Since the CV I am making will display 5 quarters I will technically need a trailing15monthaverage base member under the view dimension. I have 2 questions:

    1.) how would you write a formula to calculate a trailing15monthavg or trailing5quarteravg? 

    2.) Would the trailingavg formula exclude months/quarters that don't have data in it? As an example, out of a 15month time span, there are only 10 months worth of G&A expenses so the denominator to calculate average is 10 instead of 15.

    Thanks.

    • PeterFu's avatar
      PeterFu
      Contributor II
      3 years ago

      Hi,

       

      Then I would create them as Dynamic Calc members in UD8. You can call them Trailing5QAvg and Trailing5QTot or something like that. See example below (not the full syntax, but you get the idea). Then you can divide and do your logic inside of the api.Data.GetCell.

       

      I have not used this, but hopefully this will help you on your way.

      Dim trailing5QAvg As String = api.Data.GetDataCell("T#QuarterPrior1(|!SelectedQuarter!|) + T#QuarterPrior2(|!SelectedQuarter!| + ... ").CellAmount

      Return trailing5QAvg

       

      Peter