Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
bhaveshp90
Contributor III

Expression not working - MTD Values

Hello, 

I have a table as shown below,

 1.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

Labels (2)
1 Solution

Accepted Solutions

Re: Expression not working - MTD Values

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

image.png

2 Replies

Re: Expression not working - MTD Values

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

image.png

bhaveshp90
Contributor III

Re: Expression not working - MTD Values

thank you @sunny_talwar Smiley Happy