Forum Discussion

DamarisE's avatar
DamarisE
New Contributor II
3 years ago

Prior Year, YTD Cube View Columns

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.

Any help would be greatly appreciated.

Thanks,

Damaris

Cube View
  • DamarisE's avatar
    DamarisE
    New Contributor II
    3 years ago

    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's avatar
    aricgresko
    Contributor III
    3 years ago

    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. 

     

     

     

     

    • DamarisE's avatar
      DamarisE
      New Contributor II
      3 years ago

      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.

    • Kit_Tyrrell's avatar
      Kit_Tyrrell
      New Contributor II
      2 years ago

      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.

      • aricgresko's avatar
        aricgresko
        Contributor III
        2 years ago

        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|