how to fill empty column based on act date

good evening,

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.

Can you share an example data set of your input and what the output should look like?

I feel like this goes into a direction that I have explored a fair bit and documented in an article on Medium that was published on Friday:

As well as in a video series on youtube…

Feel free to check out those resources (and sorry for shameless self-promotion :wink: ).

If you can share a data set (anonymised, but reflecting the general structure), I’d be happy to see if I can built a prototype.

5 Likes

Hi Martin,

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

Hey Simone,

thanks for the data - I take the inputs are those in red rectangles and the values to be calculated based on the inputs in green rectangles:

I’ve created this prototype:

Workflow:

remRev.knwf (107.6 KB)

3 Likes

Hi Martin,

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?

thank you
Simone

Hey there,

well if you can share the workflow where you have an error I can certainly take a look.

I double-checked the workflow I provided and no issues there…

2 Likes

i only saved yours.
image

Anyway being the missing month already calculated, is it possible to modify your prototype

Ciao
Simo

That is weird.

This column is added by the previous node…

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?

yes i would have the aggregate.
maybe the correct name is PeriodNO?

1 Like

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.

Overview:

Workflow:

remRev.knwf (105.2 KB)

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

simo

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…

here’s the update:

remRev.knwf (108.8 KB)

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?

ciao
Simo

You want a column that always shows the current date?

E.g. you run your workflow today it shows 14/02/25, you run it tomorrow it shows 15/2/25?

You can do this e.g. with using Column Expressions node:

1 Like

perfect, thank you Martin

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.