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.
Any suggestions please
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 |
Thank you in advance
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
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.
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 |
Thank you in advance
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 |
Please help me with this, Thank you very much in advance