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.
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)
)
Try this
Aggr(
Sum({<FMonth, FY, Year, fMonth, Month, MonthYear>} FTE)
/
(Top(Sum({<FMonth, FY, Year, fMonth, Month, MonthYear>} FTE))/100)
, [Staff Group], MonthYear)
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)
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)
Unfortunately that produces no results at all!
How is MonthYear created in the script?
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)
Still nothing.MonthYear is created in the mastercalendar
Date(MonthStart(PeriodDate),'MMM-YYYY') as MonthYear
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)
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.