Annualized YTD expression in chart object

Mon, 05 Oct 2015 19:01:02 GMT

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!

topic Annualized YTD expression in chart object in QlikView

Annualized YTD expression in chart object