Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the table as shown below with YTD and MTD as expressions.
MTD = (Current month YTD) - (Previous month YTD).
My expression for MTD = YTD - Alt(Above(Total(YTD)), 0).
This expression is calculating correctly when selecting individual project number, but for all the project numbers selected It is calculating on per row basis which is completely wrong as shown in the below image;
Any idea on how to avoid it? (I want to calculate MTD as current month YTD - previous month YTD per Project_Number)
attached are files for your reference;
thanks
Once again, I don't really know what the issue is? I see repeating numbers for YTD and MTD.... but that has to do with the YTD expression and not MTD... because MTD is just using YTD... if YTD is not right, then you need to start a new discussion to talk about YTD... because MTD seems to be doing what you asked for... unless I am missing something here?
stalwar1, the YTD problem is because of the old qvd file I'm loading before.
Now my YTD values are correct, but my MTD is still same as YTD; I have used your expression;
Can you tell me what is wrong in MTD here?
thanks a lot
Two issues
1) I don't think you are using the most recent expression I gave in the response you have marked as correct
2) You are not making selection in Month field so, you will have to ignore selection in the Month field as well in addition to MonthYear and Snapshot_Date.
If you fix the above two things, your expression should work.
I am using the same expression now, and filtering based on Month Year, but not able to get the desired output
Can you attach the new version
Sure, please find the files in the attachments
Here try this....
Aggr(If(Only({1} Project_Number) = Above(TOTAL Only({1} Project_Number)),
If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)',
0,
Sum({<Cost_Code = {'3'}>}Updated_OEC_Budget)
-Sum({<Cost_Code = {'3'}>}Forecast_Cost_to_Complete)
-If(IsNull(Variance_YTD),
0,
Prior_Updated_OEC_Budget - Prior_Forecast_Cost_to_Complete))
-
Alt(Above(TOTAL
If(Only({<MonthYear, Snapshot_Date>}Num_Month_Project_Cost) = '$(vPreYearLastMonth)' and Only({<MonthYear, Snapshot_Date>}Year) = '$(vPreYear)',
0,
Sum({<Cost_Code = {'3'}, MonthYear, Snapshot_Date>}Updated_OEC_Budget)
-Sum({<Cost_Code = {'3'}, MonthYear, Snapshot_Date>}Forecast_Cost_to_Complete)
-If(IsNull(Only({<MonthYear, Snapshot_Date>}Variance_YTD)),
0,
Only({<MonthYear, Snapshot_Date>}Prior_Updated_OEC_Budget - Prior_Forecast_Cost_to_Complete)))
),0),
If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)',
0,
Sum({<Cost_Code = {'3'}>}Updated_OEC_Budget)
-Sum({<Cost_Code = {'3'}>}Forecast_Cost_to_Complete)
-If(IsNull(Variance_YTD),
0,
Prior_Updated_OEC_Budget - Prior_Forecast_Cost_to_Complete))
), Project_Number, MonthYear)
Had to clear up your expression for all those unnecessary Num() which were making your expression unreadable. Select in MonthYear field and Not Month field
thank you stalwar1. This is what I am looking for exactly
Can we not make 2016 and 2017 year selection here?
What happens when you select 2016 or 2017?