Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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).
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)
)