Expression not working - MTD Values

bhaveshp90 — Sat, 16 Nov 2024 04:33:21 GMT

Hello,

I have a table as shown below,

Where my MTD = (Current month YTD) - (Previous month YTD)

Eg: for January -2018, MTD = (-12865) - 0 = -12865

for February - 2018 , MTD = (-32765) - (-12865) = 19900

What wrong in this expression? I've MonthYear and project number as dimensions.

Attached is the file for reference

Aggr(If(Only({1} Project_Number) = Above(TOTAL Only({1} Project_Number)),

If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 
0,
(If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 0, Sum(Updated_OEC_Budget)))
-
(If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 0, Sum(Forecast_Cost_to_Complete)))
-
(
Column(1)
))

- 

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(Updated_OEC_Budget)))
	-
	(If(Only({<MonthYear, Snapshot_Date>}Num_Month_Project_Cost) = '$(vPreYearLastMonth)' and Only({<MonthYear, Snapshot_Date>}Year) = '$(vPreYear)', 0, Sum(Forecast_Cost_to_Complete)))
	- 
	Only({<MonthYear,Snapshot_Date>}Column(1)))

),0),

	If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 0,
	
	(If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 0, Sum(Updated_OEC_Budget)))
	-
	(If(Num_Month_Project_Cost = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 0, Sum(Forecast_Cost_to_Complete)))
	-
	(
	Column(1)
	))

), Project_Number, MonthYear)

thanks

Bhavesh

Re: Expression not working - MTD Values

sunny_talwar — Tue, 05 Feb 2019 16:03:18 GMT

You have use Column(1) in your expression with Aggr()... Aggr() function doesn't recognize Column reference and hence you will need to replace the column reference with the expression itself...

I have updated the app, but make sure to add the set analysis to ignore MonthYear, Snapshot_Date where we have used the expression instead of the Column function

Re: Expression not working - MTD Values

bhaveshp90 — Tue, 05 Feb 2019 20:54:17 GMT

thank you @sunny_talwar

topic Expression not working - MTD Values in QlikView

Expression not working - MTD Values

Re: Expression not working - MTD Values

Re: Expression not working - MTD Values