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.
Solved! Go to Solution.
@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.
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 :)
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
Hi @User12
Instead of using month != 1, you can use left(month, 3) != 'Jan'
Take a look at the attached workflow.
Thank you a lot!