Building a CV with a trailing 5 quarter view

CCI01
New Contributor

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 previous quarters. 

Ex. User selects 2022Q1 and then CV displays the following columns 2021Q1, 2021Q2, 2021Q3, 2021Q4, 2022Q1.

Data would be displayed quarterly in each column.

Any suggestions on how to write columns based on this criteria?

Thanks.

1 ACCEPTED SOLUTION

PeterFu
Contributor II

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.

PeterFu_0-1657652468969.png

 

PeterFu_1-1657652493965.png

 

PeterFu_2-1657652521611.png

 

Peter

 

View solution in original post

6 REPLIES 6

PeterFu
Contributor II

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.

PeterFu_0-1657652468969.png

 

PeterFu_1-1657652493965.png

 

PeterFu_2-1657652521611.png

 

Peter

 

CCI01
New Contributor

Thanks for that! 

CCI01
New Contributor

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
Contributor II

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.

 

PeterFu_0-1658211385296.png

 

Peter

CCI01
New Contributor

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
Contributor II

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

Please sign in! CCI01