I would like to be able, based on the month I am in, to feed the columns of the following months, based on how long a contract lasts.
ex:
it’s January, I have 3 months of contract and each month is worth 100, I would like it to feed the February, March and April column with 100.
another contract is worth 200 each month, and the contract expires in 5 months, so I need to feed equal amounts from February to June.
is there any way? i have created columns with value 1 with all the months i don’t know if it will help.
thank you for the reply.
please find the example. example.xlsx (11.1 KB)
I would like based on the missing revenue and the missing months to the end of the contract, it would calculate and populate the months column by dividing the revenue by the missing months.
so if we are at the end of January and I have 3 months to the end of the contract with 300 total missing revenue, it populates the feb, mar and april columns, by 100 (300/3), with the other months columns 0.
if I have a contract that expires in 5 months and is worth 1000 populate Feb to June with 200 per column (1000/5), with the other months zero.
My fiscal year runs from October to September.
thank you
first of all thanks for helping and supporting me.
regarding your question, the column missing month is already calculated, so i think is not necessary the first part of your prototype.
I removed the other info, but consider that each row also has the project name, client name, the sales, etc.
I also encountered a problem with the last node (pivot), there is an error. in the configuration → pivot → month name does not exist, could you please check?
If I adjusted the piece where I do not calculate the number of months that are missing that does not make the latter part with month name redundant… you still want to aggregate by month right?
You could use that to get a numeric representation.
I’ve just removed the part where the number of periods is calculated and taken the opportunity to change the column name to “mon”.
Hopefully that works now.
Obviously feel free to adjust it to your needs - if your “real” input data set has more columns that you want to see (e.g. contract number or customer name) then just make sure you add those as group columns in the pivot node and they should show up.
i also have another problem, the node cell split if i have few row ok, but i’m working with a long list of contract, so if i put 6 after comma sometimes the number could be in 7 position or 8 and so on… how can i set base different long text in the cell? thank you
I see. You can also use normal cell splitter and split by “_”. That should work irrespective of the number of contracts you have on your list.
Just some feedback: It’s always best if you have an example that gets as close as possible to what your input is like… then any solution provided here can accommodate…
thank you Martin.
all is clear, but one question. in the example I gave you a column with the actday we were in, but how do I create a column with the current day that changes based on the day I am in?
).
