01-05-2024 05:28 PM
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
01-09-2024 05:24 AM
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.