I am almost done with a project that I have been working on and I wouldn’t have reached so far without the support of the community. Unfortunately I am currently stuck in the following:
I have an input in the format below:
And I would like to generate the following output:
Basically I need to sum the 1st 3 weeks of each item and assign the sum to a new column and sum the 1st 5 weeks of every item and assign the sum in a new column. The data is already sorted based on weeks so no need for sorting.
I tried with pivoting but unfortunately I wasn’t able to reach the desired output. Can someone help me with such a workflow?
@ScottF Thanks a lot for your time and your reply. Unfortunately the input data (weeks) column doesn’t always have the values 1-6.
It might have 10-99 or any values basically so is it possible to modify the workflow to filter the 1st (3) and (5) rows regardless of the numbers stated in the (weeks) column? The weeks column is always sorted in ascending order.
@ScottF Please have a look at the modified input below.
As you can see the weeks value don’t have to be 1 to 6. It might take any other values.
What is guaranteed is that it is always arranged in ascending order and for every item there are the same number of weeks (no. of rows for every item) regardless of the week numbers.