sum mulitple rows on the basis of values in other fileds

[RaghavanR]

I have table which contains sales numbers and Balance numbers on a daily basis. I have to prepare a weekly report by considering weekend as every friday.

Balances are non- cumulative in nature meaning , for the particular week, I have to take balance as of Friday as the weekly balance whereas for Sales, weekly sales will be the sum of all the days sales in a week.

I have attached sample excel file. Column named "WK Amount" highlighted in yellow is what i am tryng to produce in Alteryx but no success.

Weekend will not always be Friday, it will be the last working day in a week if Friday happens to be a bank holiday. Also if a week starts with Bank holidays, the subsequent day will be counted as start of the day.

Especially during the week from 06th April 2020 to 10th April 2020, Friday is a bank holiday so that will have only 4 days starting from 1 to 4

the subsequent week starting from 13th April 2020 to 17th April 2020, Monday is a bank holiday so that will have only 4 days starting from 1 (being Tuesday) to 4.

Can some one please help me how can i achieve this alteryx? 

Thanks in advance! 

Raghavan 

[ArtApa · Accepted solution]

Hi @RaghavanR - Hope I understood your task correctly. Please find attached workflow.

[RaghavanR]

Thanks ArtApa for your kind and quick help. I am new to Alteryx and trying to replicate the same with my own workflow by using the logic you applied in the workflow. I will keep you posted. Thanks again for your quick help!

[RaghavanR]

Sorry @ArtApa instead of marking your answer as Solution I have marked my question as solution. I couldn't correct it. Is there a way I can change it? I am extremely sorry!

[RaghavanR]

Thanks @ArtApa, your solution worked like a wonder. I have tweaked it to include few other identifiers under Balance category and Sales category. Good Experience! Thanks again for your quick and fantastic help!