Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bhaveshp90
Creator III
Creator 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
sunny_talwar

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

View solution in original post

2 Replies
sunny_talwar

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
Creator III
Creator III
Author

thank you @sunny_talwar Smiley Happy