How to calculate?

[sculptorlv]

Sorry, didn'f find normal name for the topic.

I have the following situation.

Table:

Order nr.     Item nr.     Item name     Quantity

1                    x              Full               1

2                    y              Empty          -1

3                    x               Full               1

3                    y               Empty          -1

4                    x               Full               1

4                    x               Full               1

4                    y                Empty          -1

We deliver to clients product - bottles, and took from cliets back empty bottles (but now always we took empty bottles).

If driver deliver to client 1 Full bottle and get back 1 Empty bottles  (+1 -1) this would count as value 1 (only one operation)

Similar, if driver deliver to client 2 Full bottle and get back 2 Empty bottles  (+2 -2) this would count as value 2 (two operations)

If driver just deliver to client full bottles, this couns as SUM ... +1 = 1

If driver just get back empty bottoels, we count as positive SUM ... - 1 = 1.

So, I need to make such calculation:

- if we only took empty bootles - than value is positive (if sum = -1, the our values =1, if -2 , then 2 etc)

- if we give cliet full bootles - than we sum values

- if within ONE order we have -1 and +1 - than means we did an exchange and the result must be 1

- if within ONE order we have -2 and +1 - than result must be 1 (-1 and 1) + 1 (fabs -1)  = 2

- if  within ONE order we have -3 and +1 than result must be 1 (-1 and 1) + 1 +1 = 3

- if  within ONE order we have -2 and +4 than result must be 2 (-2 and +2) + 1 +1 = 4

How can I make such calcutaions.

Thank you in advance!

[sunny_talwar]

Are you wanting to do this in the script or front end?

[sculptorlv]

I want to do it in Straight table (front end?), not in SQL script.

[sunny_talwar · Accepted solution]

May be this?

=RangeMax(Sum({<[Item nr.] = {'x'}>}Quantity), fabs(Sum({<[Item nr.] = {'y'}>}Quantity)))

[sculptorlv]

Thank you, I will check.

Can you please explain, that does the following brackets??

{.......}

<.....>