Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
The Expression MTD is showing the correct value as shown in the below image. But when a Month field is selected it is becoming a null value.
When September-2018 is selected it is showing null value
What I am missing in my MTD expression, attached is the file for reference.
Aggr(If(Only({1} Project_Number) = Above(TOTAL Only({1} Project_Number)), ( ( if(Cost_Code=8,Sum({<Cost_Code={8}>}Prior_Updated_OEC_Budget), if(Cost_Code=10,Sum({<Cost_Code={10}>}Prior_Updated_OEC_Budget), if(Cost_Code=12,Sum({<Cost_Code={12}>}Prior_Updated_OEC_Budget), Sum({<Cost_Code={8,10,12}>}Prior_Updated_OEC_Budget)))) - if(Cost_Code=8,Sum({<Cost_Code={8}>}Prior_Forecast_Cost_to_Complete), if(Cost_Code=10,Sum({<Cost_Code={10}>}Prior_Forecast_Cost_to_Complete), if(Cost_Code=12,Sum({<Cost_Code={12}>}Prior_Forecast_Cost_to_Complete), Sum({<Cost_Code={8,10,12}>}Prior_Forecast_Cost_to_Complete)))) ) * ( If(Cost_Code=8, Sum({<Cost_Code={8}>}POC)/100 - Above(TOTAL Sum({<MonthYear,Year,YearMonth,Cost_Code={8}>}POC)/100, Num(Month)), If(Cost_Code=10, Sum({<Cost_Code={10}>}POC)/100 - Above(TOTAL Sum({<MonthYear,Year,YearMonth,Cost_Code={10}>}POC)/100, Num(Month)), If(Cost_Code=12, Sum({<Cost_Code={12}>}POC)/100 - Above(TOTAL Sum({<MonthYear,Year,YearMonth,Cost_Code={12}>}POC)/100, Num(Month)), Sum({<Cost_Code={8,10,12}>}POC)/300 - Above(TOTAL Sum({<MonthYear,Year,YearMonth,Cost_Code={8,10,12}>}POC)/300, Num(Month))))) ) ) - Alt(Above(TOTAL (( if(Only({<MonthYear, Snapshot_Date>}Cost_Code)=8,Sum({<MonthYear, Snapshot_Date,Cost_Code={8}>}Prior_Updated_OEC_Budget), if(Only({<MonthYear, Snapshot_Date>}Cost_Code)=10,Sum({<MonthYear, Snapshot_Date,Cost_Code={10}>}Prior_Updated_OEC_Budget), if(Only({<MonthYear, Snapshot_Date>}Cost_Code)=12,Sum({<MonthYear, Snapshot_Date,Cost_Code={12}>}Prior_Updated_OEC_Budget), Sum({<MonthYear, Snapshot_Date,Cost_Code={8,10,12}>}Prior_Updated_OEC_Budget)))) - if(Only({<MonthYear, Snapshot_Date>}Cost_Code)=8,Sum({<MonthYear, Snapshot_Date,Cost_Code={8}>}Prior_Forecast_Cost_to_Complete), if(Only({<MonthYear, Snapshot_Date>}Cost_Code)=10,Sum({<MonthYear, Snapshot_Date,Cost_Code={10}>}Prior_Forecast_Cost_to_Complete), if(Only({<MonthYear, Snapshot_Date>}Cost_Code)=12,Sum({<MonthYear, Snapshot_Date,Cost_Code={12}>}Prior_Forecast_Cost_to_Complete), Sum({<MonthYear, Snapshot_Date,Cost_Code={8,10,12}>}Prior_Forecast_Cost_to_Complete)))) ) * ( If(Only({<MonthYear, Snapshot_Date>}Cost_Code)=8, Sum({<MonthYear, Snapshot_Date,Cost_Code={8}>}POC)/100 - Above(TOTAL Sum({<MonthYear,Year,Snapshot_Date,YearMonth,Cost_Code={8}>}POC)/100, Num(Month)), If(Only({<MonthYear, Snapshot_Date>}Cost_Code)=10, Sum({<MonthYear, Snapshot_Date,Cost_Code={10}>}POC)/100 - Above(TOTAL Sum({<MonthYear,Year,Snapshot_Date,YearMonth,Cost_Code={10}>}POC)/100, Num(Month)), If(Only({<MonthYear, Snapshot_Date>}Cost_Code)=12, Sum({<MonthYear, Snapshot_Date,Cost_Code={12}>}POC)/100 - Above(TOTAL Sum({<MonthYear,Year,Snapshot_Date,YearMonth,Cost_Code={12}>}POC)/100, Num(Month)), Sum({<MonthYear, Snapshot_Date,Cost_Code={8,10,12}>}POC)/300 - Above(TOTAL Sum({<MonthYear,Year, Snapshot_Date,MonthYear,YearMonth,Cost_Code={8,10,12}>}POC)/300, Num(Month))))) )) ),0), ( ( if(Cost_Code=8,Sum({<Cost_Code={8}>}Prior_Updated_OEC_Budget), if(Cost_Code=10,Sum({<Cost_Code={10}>}Prior_Updated_OEC_Budget), if(Cost_Code=12,Sum({<Cost_Code={12}>}Prior_Updated_OEC_Budget), Sum({<Cost_Code={8,10,12}>}Prior_Updated_OEC_Budget)))) - if(Cost_Code=8,Sum({<Cost_Code={8}>}Prior_Forecast_Cost_to_Complete), if(Cost_Code=10,Sum({<Cost_Code={10}>}Prior_Forecast_Cost_to_Complete), if(Cost_Code=12,Sum({<Cost_Code={12}>}Prior_Forecast_Cost_to_Complete), Sum({<Cost_Code={8,10,12}>}Prior_Forecast_Cost_to_Complete)))) ) * ( If(Cost_Code=8, Sum({<Cost_Code={8}>}POC)/100 - Above(TOTAL Sum({<MonthYear,Year,YearMonth,Cost_Code={8}>}POC)/100, Num(Month)), If(Cost_Code=10, Sum({<Cost_Code={10}>}POC)/100 - Above(TOTAL Sum({<MonthYear,Year,YearMonth,Cost_Code={10}>}POC)/100, Num(Month)), If(Cost_Code=12, Sum({<Cost_Code={12}>}POC)/100 - Above(TOTAL Sum({<MonthYear,Year,YearMonth,Cost_Code={12}>}POC)/100, Num(Month)), Sum({<Cost_Code={8,10,12}>}POC)/300 - Above(TOTAL Sum({<MonthYear,Year,YearMonth,Cost_Code={8,10,12}>}POC)/300, Num(Month))))) ) ) ), Project_Number, Year,MonthYear)
thanks
Bhavesh
It is because MTD Impact formula uses Above function. Once you select a particular month there is no other visible record in the table hence it is not able to do calculation.
Ideally you should use Master Calendar with AsOfMonth functionality. Also instead of having such a complicated calculation in the UI it should be calculated in the script.