alteryx Community

# Alteryx Designer Discussions

## offset the -ve value from the first positive value

8 - Asteroid

Hello everyone

I am working on a workflow where i have to do do calculation on the below data, i need to offset the -ve value from the first positive value for each ID.

Input

 ID Month Data 1 March 10 1 April -5 2 Jan 10 2 Feb 12 2 March -5 3 Jan -5 3 Feb 10 3 March 12 4 Jan 10 4 Feb 15 4 March -20

Output - This is the expected output after the calculation

 ID Month Data 1 March -5 2 Jan 5 2 Feb 12 3 Feb 5 3 March 12 4 Feb 5

5 REPLIES 5
13 - Pulsar

hey @Ekta
Would love to help but I don't think I understand exactly the calculation required. What do you mean by 'offset hte -ve value from the first positive value for each ID'. Unless I'm mistaken, should the first row of output be:

 ID Month Data 1 March 5

Given the input:

 ID Month Data 1 March 10 1 April -5

my thinking is the first positive number (10), take away the negative value (-5), would be '5', not '-5'.

Am i misunderstanding?

Cheers,
TheOC

16 - Nebula

in addition to what @TheOC said, how can you have multiple lines per ID if you're off-setting against the first positive value? There is always only one first...of anything.

M.

16 - Nebula

Hi @Ekta ,

I think you missed a few things out here, as in you don't simply off-set against the first positive value, but you off-set the amount against the first value, then the remainder against the next value, and the remainder against the next value etc. until the there is no negative value left. Once the amount has been subsumed by the negative value then it needs to be removed.

And I assume you got the first value wrong, and you meant 5 not -5, otherwise none of it makes sense.

Anyway, I've attached the workflow. Basically I used a sum and join to associate the negative value back to the appropriate IDs, then a multi-row formula to assign the negative balance.

Hope this helps,

M.

8 - Asteroid

Hi @TheOC , Thanks so much for your reply, apologies for not being very clear in asking question.

basically what i want to achieve is -

if my input is this

 ID Month Data 5 7 65 5 8 44 5 9 -10 5 10 28 5 11 -4 5 12 -13

my required output is - sum of all the negative numbers (-10,-4,-13), we subtract from 1st month , in this case  65-27 = 38 and rest all rows remains same.

 ID Month Data 5 7 38 5 8 44 5 9 0 5 10 28 5 11 0 5 12 0

but in this case - since the first month value is less than sum of negatives 25 < 27

 ID Month Data 5 7 25 5 8 44 5 9 -10 5 10 28 5 11 -4 5 12 -13

Output is - means  sum of all the negative numbers (-10,-4,-13  =  -27), we subtract from 1st month and left over one from second month , here 25-25 = 0 , and then 44-2 =42

 ID Month Data 5 7 0 5 8 42 5 9 0 5 10 28 5 11 0 5 12 0

8 - Asteroid

Hi @mceleavey  , Thanks so much for your reply and the workflow and yes you are right , my first value in output is wrong it should be 5 not -5 😞

The workflow is working accurately on the dataset i provided but when i tried it on my full data , i am not getting accurate output:( don't know what i am missing.

basically what i want to achieve is -

if my input is this

 ID Month Data 5 7 65 5 8 44 5 9 -10 5 10 28 5 11 -4 5 12 -13

Output i am getting is (which is incorrect)

 ID Month Data 5 7 52 5 8 44 5 10 18

my required output is - sum of all the negative numbers (-10,-4,-13), we subtract from 1st month , in this case  65-27 = 38 and rest all rows remains same.

 ID Month Data 5 7 38 5 8 44 5 9 0 5 10 28 5 11 0 5 12 0