Read and minus data vertically

[SH_94]

Dear community,

I faced some challenges in building the alteryx workflow as the data presented were different as compared to normal. Below are query on how to build the workflow.

1. Normally we can minus the data against different columns. However, my data need to minus against different rows. May i know how can we do in this case? For example in the case below, in order to derive cell C50, i need to take C9 and minus against C17,C22,C27,C32 and C37 to arrive the number at C50. I also attached the excel in the working below.

May i know if anyone experience before and how to build this workflow? Row 50 to 60 are my results (after deduction of all relevant row)

2. The second challenge is the data need to be calculated on weekly basis. The data from row 3 to row 34 are constant for all the four week. While from row 36 to row 44, the data show by weekly basis. I would need the result data at weekly basis as shown in the excel file from row 50 to 60.

The above concept apply to all the columns.

It would be highly appreciated if you could assist to build sample workflow on how to derive the final output.

Many thanks for your help in advance.

[ArtApa]

Hi @SH_94 - This is how you can calculate by Week. You can then bring the result back using a Union tool.

[SH_94]

Hi @ArtApa ,

Many thanks for the response and it is really amazing and so creative the solution.

May i ask about the concept for the following formula used? Could you briefly explain on this?

Many thanks again for the help.

[ArtApa]

Hi @SH_94 - Sure.

1) The first Multi-Row Formula tool creates the Flag column and puts values based on a condition, which can be read as follows:

If the first word (in Alteryx the first position is 0) is one of the following: ("Price", "Fixed", "Week") then the entire value is copied from F2 to flag. Otherwise the value is copied from the row above.

I use the Flag column later in the Filter.

2) I use this condition to filter the rows that are required for calculations:

a) IsNull([F2]) and !IsNull([F3]) - B9 is null and C9 is not null ("!" means NOT)

b) Or rows with the value "PN". 

This is only for your data and the way it is formatted. The result looks like:

Hope that explains the logic. 

[SH_94]

Hi @ArtApa ,

Many thanks for the prompt response. 

I just finish analysing the workflow and would like to ask the following question

1. Could you show me how can we do for TSS and YYY (combined with PN) ? As i know how to individually doing it but dunno how to do three of them concurrently.

2. In the result of the workflow, it only covers F3 column and may i know how can i do it for all the column? As the data have the column till F11. Could you guide me on how to expand the solution till F11 column?

Many thanks again for the help.

[ArtApa]

Hi @SH_94 - I adjusted the workflow to do both:

If you're happy, please mark it as solved to close the case.

[atcodedog05]

Hi @SH_94 

My Take on this. Guess it ended similar to above 😅

Workflow:

Hope this helps : )

[SH_94]

Hi @atcodedog05 ,

Many thanks for the prompt response.

Sorry to update you that i just realised the formula for the below is different with other column. If there are any amount for row 12 , 13 and 14, we will take the amount instead of taking the row 9. May i know how can we fix the workflow if there are additional condition in this case?

[ArtApa]

Hi @SH_94 - I changed it quickly. It's not very elegant but gives a desired output. Cheers,

[atcodedog05]

Hi @SH_94 

Here is my take on it.

Workflow:

Hope this helps : )