Prior Year, YTD Cube View Columns

DamarisE
New Contributor II

Hi All,

I am creating a cube view with a 'Current Year' column and a 'Prior Year' column based on the POV (this company has an August close fiscal year).  These columns work if I am viewing full year over full year, but if I want to view Feb 2022 YTD vs Feb 2021 YTD, it does not work for the prior year.  The values returned for 2021 only show the full year.  Is there a way to dynamically pull 2021 YTD data based on POV?

Attached is a snapshot of what I currently have tried.

DamarisE_0-1660844053341.png

Any help would be greatly appreciated.

Thanks,

Damaris

6 REPLIES 6

DamarisE
New Contributor II

Hi everyone,  My team and I figured this out.  We ended up using: T#YearPrior1(|CVTime|)Period(|CVTime|) to pull current period prior year data.  Curious as to why this is not in the design document, this is a standard reporting requirement.

Thanks,

Damaris

 

 

aricgresko
Contributor III

Try this setup below if you want to make the report more dynamic for the users in terms of selecting the period and selecting the view dimension, unless your report requires it to be run based on user POV.

Note: |!ParamMonth!| is my parameter to select the month for the first column.

          |!ParamView!| is my parameter to let the user select Periodic, MTD, QTD, or YTD. 

aricgresko_3-1660852494381.png

 

aricgresko_4-1660852516332.png

 

aricgresko_2-1660852410216.png

 

 

DamarisE
New Contributor II

Thank you for this!  We have our set up so that it does have to run based on POV, but I will keep this option for future projects.

Hi, I'm new to Onestream and I am trying to do something similar, but in Rows I'd like to display all the periods of a given selected year and then all the periods of the prior year.  I've been unable to get this to work.   Columns   Year1, Year2.   Row1 T#CVYear.Months.   Any thoughts or suggestions would be appreciated.

Hi Kit - I'm a bit confused with what you're specifically asking for, but you need the pipes symbol to accomplish it, if you are using the substitution variable of CVYear.

T#|CVYear|.Months would be the correct syntax to get all months in the Cube View Year.  To get the prior year's months, try T#PeriodPrior1(|CVYear|).Months.  

I'm not sure what this cube view is being used for, but I typically like to allow the time period to be selectable for the end user (unless it's forms related where I typically use T#WF).  If you want to make it more dynamic, throw in a parameter, such as |!ParamMonth!| in place of |CVYear|

Thank you very much for the reply.   I'm trying to get to a CV that would display 2 columns of data, Current Year and Prior Year for each of the 12 periods of a year.

Kit_Tyrrell_0-1678741568616.png

On rows, I am just displaying the CY and Months.

Kit_Tyrrell_1-1678741703345.png

I thought I could then override the row for prior year with something like this:

Kit_Tyrrell_2-1678741762161.png

I've not been able to get that to display though.  I have seen in most examples folks are doing similar things by displaying the periods in columns vs row, but I have users use to seeing the periods in rows, so was trying to replicate that.  Any thoughts are greatly appreciated.