How to calculate?

sculptorlv — Wed, 25 Nov 2020 16:16:04 GMT

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 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!

Re: How to calculate?

sunny_talwar — Tue, 29 Mar 2016 11:47:53 GMT

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

sculptorlv — Tue, 29 Mar 2016 11:49:39 GMT

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

sunny_talwar — Tue, 29 Mar 2016 12:03:07 GMT

May be this?

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

sculptorlv — Tue, 29 Mar 2016 12:13:48 GMT

Thank you, I will check.

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

{.......}

<.....>