Dimension order in pivot table ruining index calculation

[zakpullen]

Hi,

I'm trying to create an index chart, with some progress. This expression works in a pivot table as long as MonthYear is the second dimension (of 2)

SUM({<FMonth=,FY=,Year=,fMonth=,Month=,MonthYear=>}FTE)
/
(top(SUM({<FMonth=,FY=,Year=,fMonth=,Month=,MonthYear=>}FTE))/100)

However, I want to present this in a line chart, say with 'Staff Group' as the other dimension.  If MonthYear is the second dimension, each line in the chart represent a MonthYear, and Staff Group in on the X axis. If I swap the dimensions around, the calculations mess up.

Is there any way around this?

Many thanks.

[zakpullen]

Actually, the expressions work if MonthYear is the second dimension, but not if it's the first. It needs to be second for the top() function to work, but in a line chart MonthYear needs to be on the X-axis (which it can't be as second dimension).

[sunny_talwar · Accepted solution]

Try this

Only({1} 

Aggr(

Sum({<FMonth, FY, Year, fMonth, Month, MonthYear = {">=$(=Date(MonthStart(Max(MonthYear), -5), 'MMM-YYYY'))"}>} FTE)
/
(Top(Sum({<FMonth, FY, Year, fMonth, Month, MonthYear = {">=$(=Date(MonthStart(Max(MonthYear), -5), 'MMM-YYYY'))"}>} FTE))/100)

, [Staff Group], MonthYear)

)