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.

[sunny_talwar]

Try this

Aggr(

Sum({<FMonth, FY, Year, fMonth, Month, MonthYear>} FTE)
/
(Top(Sum({<FMonth, FY, Year, fMonth, Month, MonthYear>} FTE))/100)

, [Staff Group], MonthYear)

[zakpullen]

Thanks Sunny. I should add that the MonthYear range is controlled with a calculated dimension (below). Using your expression restricts results to the latest month only, and the results  are different (bigger, not wildly so).

If(MonthYear >= AddMonths(Max(TOTAL MonthYear), -5), MonthYear)

[sunny_talwar]

May be do it in the set analysis

Aggr(

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

, [Staff Group], MonthYear)

[zakpullen]

Unfortunately that produces no results at all!

[sunny_talwar]

How is MonthYear created in the script?

[sunny_talwar]

Also, I added the incorrect sign <=... should be >=

Aggr(

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

, [Staff Group], MonthYear)

[zakpullen]

Still nothing.MonthYear is created in the mastercalendar

Date(MonthStart(PeriodDate),'MMM-YYYY') as MonthYear

[sunny_talwar]

Try this

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)

[zakpullen]

Now shows the latest (selected) MonthYear, with the correct value against each Staff Group. The expression is calculating correctly, but needs to display the previous 5 months.

Thanks for looking at this.