Forum Discussion

MP1's avatar
MP1
New Contributor
2 years ago

Quarter to Date display in cube view

I am trying to build a cube view where the columns represent the 4 quarters in the year and then a YTD column.

So if the POV is 2023M8 then I would like to see Q1 2023, Q22023, Q3 (should be M7+M8) and Q4 should be zero.

I have tried this but it is still displaying the total for q3 even if my pov is M8 it is still picking up M9

T#Quarter(|PovTime|).allpriorinyearinclusive:V#periodic

my next column has this T#Quarter(|PovTime|).allnextinyear:V#periodic but it is still showing the YTD reversal

any suggestions?

thanks

Cube View

1 Reply

Sort By
  • JackLacava's avatar
    JackLacava
    Community Manager
    2 years ago

    AllPriorInYearInclusive works according to the frequency of the member you're appending it to. So if you put it on a quarter member, you're asking for "all the QUARTERS in the year previous to this", and you'll get a total for the full quarter.

    The YTD reversal is derived data, so you can hide it with conditional formatting:

    If (IsDerivedData = true) Then
    TextColor = White
    BackgroundColor = White
end if

    I don't know how you'd build the exact CV you're asking for, I have a feeling there is a simple solution that is escaping me; at worst, you can resort to an XFBR or Custom Member List to calculate the values you need.