Expression is showing null when selecting the Month field

bhaveshp90 — Sat, 16 Nov 2024 04:28:01 GMT

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

Re: Expression is showing null when selecting the Month field

Vegar — Wed, 13 Feb 2019 21:43:03 GMT

Wow that was a tough expression to follow when reading the community on a mobile device.

I would recommend you not to identify the/a sub part of your expression that is causing you trouble.

When you've done that you might understand the root cause your self, if not then post this limited sub part to this forum for help.

Good luck!

topic Expression is showing null when selecting the Month field in QlikView

Expression is showing null when selecting the Month field

Re: Expression is showing null when selecting the Month field