Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I've the sample test table as shown below
My MTD total -427,884 is correct as shown above (with selected Cost_Code = 3), When I create another expression with Cost_Code =3 in the set analysis, the total is incorrect as highlighted above.
What is wrong in my "Cost_Code=3" expression? attached is the file for reference.
Aggr(If(Only({1} Country_Name) = Above(TOTAL Only({1} Country_Name)), If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 0, (If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 0, Sum(TOTAL<Region1,Country_Name>{<Cost_Code={3}>}Updated_OEC_Budget_USD))) - (If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 0, Sum(TOTAL<Region1,Country_Name>{<Cost_Code={3}>}Forecast_Cost_to_Complete_USD))) - ( Sum(TOTAL<Region1,Country_Name>{<Cost_Code={3}>}Prior_Updated_OEC_Budget_USD) - Sum(TOTAL<Region1,Country_Name>{<Cost_Code={3}>}Prior_Forecast_Cost_to_Complete_USD) )) - 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({<MonthYear, Snapshot_Date, Cost_Code={3}>}Updated_OEC_Budget_USD))) - (If(Only({<MonthYear,Snapshot_Date>}Num_Month_Project_Cost) = '$(vPreYearLastMonth)' and Only({<MonthYear,Snapshot_Date>}Year) = '$(vPreYear)', 0, Sum({<MonthYear, Snapshot_Date, Cost_Code={3}>}Forecast_Cost_to_Complete_USD))) - ((Sum({<MonthYear,Snapshot_Date,Cost_Code={3}>}Prior_Updated_OEC_Budget_USD)) - Sum({<MonthYear, Snapshot_Date,Cost_Code={3}>}Prior_Forecast_Cost_to_Complete_USD))) ),0), If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 0, (If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 0, Sum(TOTAL<Region1,Country_Name>{<Cost_Code={3}>}Updated_OEC_Budget_USD))) - (If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 0, Sum(TOTAL<Region1,Country_Name>{<Cost_Code={3}>}Forecast_Cost_to_Complete_USD))) - ( (Sum(TOTAL<Region1,Country_Name>{<Cost_Code={3}>}Prior_Updated_OEC_Budget_USD) - Sum(TOTAL<Region1,Country_Name>{<Cost_Code={3}>}Prior_Forecast_Cost_to_Complete_USD)) )) ), Country_Name,MonthYear,Region1)
thanks
Bhavesh
Check attached
Check attached
You are Awesome man , thank you so much @sunny_talwar