Aggr with condition

[Cantelli]

Hello,

I'm trying to add a column (total dif) showing the value aggr of difference between "Objective" and "Actual" values, if that is greater than 0.

Ex.:

Client	Objective	Actual	Difference	Total Dif.
A01	106		106	106
A01	976	2230	0	106
A01	420	742	0	106

[Steven_Haught]

Try this it worked for me:

=(sum(total if(Objective-Actual>0,Objective-Actual)))

[Cantelli]

Steven, thank you for the answer.

It isn't worked, but it's my fault. Let me show you more information.

That is my complete base. The column "Result" is your solution. "Total Dif" is my target. 

Client	Group	Sub Group	Objective	Actual	Difference	Total Dif.	Result
A01	115	W	26	23	3	3	91
A01	217	P	106	0	106	106	91
A01	217	S	976	2.230	0		91
A01	217	M	420	742	0		91
A02	237	M	210	40	170	382	91
A02	237	S	226	60	166	382	91
A02	237	P	66	20	46	382	91

[Steven_Haught · Accepted solution]

@Cantelli 

Ok, I do think your math in that table is slightly off but I think I have figured this out.  @sunny_talwar did this in another similar situation, so don't want to take full credit! 

=RangeSum(Above(TOTAL Sum(Difference), 0, Aggr(Dim, Group, SubGroup))) 

This should work for you. This is doing two things in the load. 

1. Since I was doing inline load I created a unique num for each record. This could be done using RowNo() in the load if you are using regular load. *see attached solution for Sunny's breakdown. 

2. I also went ahead and created the Difference field in load so I could reference in the formula. 

Resulting table will look like this:

Dim	Client	Group	SubGroup	Objective	Actual	Difference	Range Sum
1	A01	115	W	26	23	3	3
2	A01	217	P	106	0	106	109
3	A01	217	S	976	2230	-	109
4	A01	217	M	420	742	-	109
5	A02	237	M	210	40	170	279
6	A02	237	S	226	60	166	445
7	A02	237	P	66	20	46	491

Previous Solution for Reference:

https://community.qlik.com/t5/QlikView-Creating-Analytics/RangeSum-with-Aggr/td-p/930324

Let me know if that works! 

Thanks,

Steven 

[Cantelli]

It's worked. Thank you @Steven_Haught