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. NumberNameJan-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19Jan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20401Revenue1970102931592121229243422510332159398104272544384491535126495101445422383299413224932647383613890339521444735070851589 What is the end result, that would be great to see: NumberNameJan-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19Jan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20401Revenue1970832356285308311376170567651291516594769211195104944792975582308398571454261849526235881 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. Example.yxzp

Substract a field from a previous

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.

Example.yxzp

Workflow

Parse

Transformation

Preparation

Accepted answers

Felipe_Ribeir0

Hi @User12

One way of doing this

Substract a field from a previous.yxmd

Felipe_Ribeir0

Hi @User12

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

Take a look at the attached workflow.

Substract a field from a previous.yxmd

All comments

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.

New Workflow2.yxmd

Felipe_Ribeir0

Hi @User12

One way of doing this

Substract a field from a previous.yxmd

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: