Expression is not working

bhaveshp90 — Sat, 16 Nov 2024 04:32:39 GMT

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

Re: Expression is not working

sunny_talwar — Wed, 06 Feb 2019 14:50:29 GMT

Check attached

Re: Expression is not working

bhaveshp90 — Wed, 06 Feb 2019 15:05:53 GMT

You are Awesome man , thank you so much @sunny_talwar

topic Expression is not working in QlikView

Expression is not working

Re: Expression is not working

Re: Expression is not working