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