Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

bhaveshp90
Contributor III

Expression is showing null when selecting the Month field

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. 

1.PNG

 

 

 

 

 

 

 

 

 

When September-2018 is selected it is showing null value

2.PNG

 

 

 

 

 

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

Labels (2)
1 Reply
Partner
Partner

Re: Expression is showing null when selecting the Month field

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!
Please ekskuse my Norglish and Swenglish typos.