Expression not working - MTD Values

[bhaveshp90]

Hello, 

I have a table as shown below,

 

Where my MTD = (Current month YTD) - (Previous month YTD)

Eg: for January -2018, MTD = (-12865) - 0 = -12865

for February - 2018 , MTD = (-32765) - (-12865) = 19900

What wrong in this expression? I've MonthYear and project number as dimensions. 

Attached is the file for reference

Aggr(If(Only({1} Project_Number) = Above(TOTAL Only({1} Project_Number)),

If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 
0,
(If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 0, Sum(Updated_OEC_Budget)))
-
(If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 0, Sum(Forecast_Cost_to_Complete)))
-
(
Column(1)
))

- 

Alt(Above(TOTAL

If(Only({<MonthYear, Snapshot_Date>}Num_Month_Project_Cost) = '$(vPreYearLastMonth)' and Only({<MonthYear, Snapshot_Date>}Year) = '$(vPreYear)', 
	0,
	(If(Only({<MonthYear, Snapshot_Date>}Num_Month_Project_Cost) = '$(vPreYearLastMonth)' and Only({<MonthYear, Snapshot_Date>}Year) = '$(vPreYear)', 0, Sum(Updated_OEC_Budget)))
	-
	(If(Only({<MonthYear, Snapshot_Date>}Num_Month_Project_Cost) = '$(vPreYearLastMonth)' and Only({<MonthYear, Snapshot_Date>}Year) = '$(vPreYear)', 0, Sum(Forecast_Cost_to_Complete)))
	- 
	Only({<MonthYear,Snapshot_Date>}Column(1)))

),0),

	If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 0,
	
	(If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 0, Sum(Updated_OEC_Budget)))
	-
	(If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 0, Sum(Forecast_Cost_to_Complete)))
	-
	(
	Column(1)
	))

), Project_Number, MonthYear)

thanks

Bhavesh

[sunny_talwar · Accepted solution]

You have use Column(1) in your expression with Aggr()... Aggr() function doesn't recognize Column reference and hence you will need to replace the column reference with the expression itself...

I have updated the app, but make sure to add the set analysis to ignore MonthYear, Snapshot_Date where we have used the expression instead of the Column function

[bhaveshp90]

thank you @sunny_talwar