Substract a field from a previous

[User12]

Hi everyone,

Thank you for reading this :)

I've attached a workflow below, so you can see, that we have a dataset with 27 fields, but 2 of them will be not included in calculations.

Our input data looks like this. It is cumulative. We need to substract current month from next.

Number	Name	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20
401	Revenue	1970	10293	15921	21229	24342	25103	32159	39810	42725	44384	49153	51264	9510	14454	22383	29941	32249	32647	38361	38903	39521	44473	50708	51589

What is the end result, that would be great to see:

Number	Name	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20
401	Revenue	1970	8323	5628	5308	3113	761	7056	7651	2915	1659	4769	2111	9510	4944	7929	7558	2308	398	5714	542	618	4952	6235	881

First 2 fields could be dynamic, so end user should be able to choose which fields are not with values.

How do I see a workflow:

Split an input data into 2 datastreams. First contains "Number" and "Name" fields, that will not be used, and second - the rest of data.

We need to substract next column from current, but there are many periods, so we don't need to do it with the first month of year, so we need to make some kind of "Row-1" formula, but with fields, that will mean "if (contains[jan-19],"19") then [feb-19]-[jan-19] else [jan-19]). 

But the trick is that [Jan-19] is first month, so how do we keep it?

P.S. We may have data with more years.

[grazitti_sapna]

@User12 , you can achieve this by using transform tools. Please find my solution and let me know if you need any assistance with the workflow.

[Felipe_Ribeir0 · Accepted solution]

Hi @User12 

One way of doing this

[User12]

Thank you for a response, but you see, I needed formula to substract values within periods, so that in result we will not do any manipulations with first month of period. In your solution we've got this:

Anyway, thank you for help :)

[User12]

Thank you for help! That's what I wanted :)

Could I ask you one more question?

Your criteria in formula is based at Date ( If month is not equal 1, then it makes calculations )

But I may have a bit more complicated input, where we do not have full date (only Jan-19,Feb-19,...Jan-20... for instance).

Maybe we can somehow let formula understand that we have 12 periods without date?

I will attach more realistic dataset

[Felipe_Ribeir0 · Accepted solution]

Hi @User12 

Instead of using month != 1, you can use left(month, 3) != 'Jan'

Take a look at the attached workflow.

[User12]

Thank you a lot!