I am trying to find the max month YTD metric value in each quarter, divide it by the corresponding month # and multiply it by 12.
An example would be looking at Q-3 2015, I want (Sep YTD Value1/9)*12/ Sep YTD Value2.
My current expression was just taking the sum of the monthly value 1 in the quarter and dividing them by the sum of monthly of value 2.
=sum({$<QuarterId= {">=$(=Max(QuarterId)-11) <=$(=Max(QuarterId))"}, MonthYear=,YearQuarter=, sOpexCat ={'Value1'}>}sOpexValue)
/sum({$<QuarterId= {">=$(=Max(QuarterId)-11) <=$(=Max(QuarterId))"}, MonthYear=,YearQuarter=, sOpexCat = {'Value2}>}sOpexValue)
As you can see the set currently displays the last 12 quarters based on the user selection, which I want to keep.
Now I have to work with Value1YTD and Value2YTD, So I need a set expression that would find the Max(MonthId) in each quarter to return the last month, then another set to identify the month number.
I tried this, but it only returns the last month reported, not all max months per the Quarter dimension.
num(Month(Max({< MonthId = {"=$(=Max(MonthId))"} >} DateNum)))
Any help is greatly appreciated!
