Calculation for specific period update

[Learner09]

Hello,

I am trying to build a flow that updates the amount for a specific period on the basis of dates. Please assist me in designing the flow. Kindly look at the details in the attached sheet.

[Peachyco]

Certainly manageable by Transposing the dataset, doing the necessary data manipulation, and then Cross-Tabbing the dataset back into its original format.

Note that I had to make some modifications to the original dataset in Excel. I aligned all the necessary columns so that their column names are in the same row in Excel:

 

This is the resulting dataset:

 

[Learner09]

@Peachyco thank you so much for the flow, it working fine, but now I have another requirement I have to update the USD at the customer level for different customers. Could you please modify the flow at the customer level? Please the example file.

[Peachyco · Accepted solution]

@Learner09 You only need to make these adjustments:

• Add [Customer] to the key columns of the Transpose Tool
• Add [Customer] to the grouping of the Cross Tab Tool
• Untick [Customer] in the Dynamic Rename Tool so that its column name doesn't get modified

You can keep following the steps above for any similar field that you need to add. The formula is only really concerned with [USD], [Start Date] and [End Date].

[Learner09]

@Peachyco thank you, it working fine, is there any way we can build a macro for this problem?

[Peachyco]

@Learner09 

The bulk of this workflow is automated so you really only need to modify the starting and the ending tools.

• This workflow starts with an Input Data Tool. You can either attach a Control Parameter Tool to it, or replace it altogether with a Macro Input Tool.

• This workflow just ends in a Browse Tool. You can swap that out for a Macro Output Tool.

With these changes, the workflow becomes a batch macro.