Dynamic Year wise view

Hello Community,

The input excel file contains various months and value against each month. I am looking for a way to extract the total sales value each year. Here's the catch, we are talking about financial years which is different with every customer. Some customer assumes Jan-Dec and some April-May. There are so many financial year combination. I am thinking of getting an "Input text" icon where I can feed the start month of the financial year and then somehow a formula to continuously add next 12 months value .

I am not sure how to move further with this logic. How to segregate and then add the values with this input text icon. Moreover the new headers to show the start and end months'year . For eg: Jan'14- Dec'14 or Aug'17 to Sept'18

I would appreciate any help. Attached is an excel file similar to input file.

Thanks!

Dynamic year view.xlsx

Best Practices

Transformation

Dynamic Processing

Accepted answers

danrh

I'd maybe look into using a formula to determine which fiscal year the record should belong to, then do a Summarize to get the total amount per year. Something like:

solution.yxmd

All comments

cmcclellan

Does each customer submit a different file, but the format is identical ?

How do you identify the different customers if the file is the same ? (based on filename ?)

Depending on your environment, I'd be inclined to create another table/excel file to hold which customer has which financial year and then use that when summarising the data.

ayadav8

@cmcclellan I am actually looking to make separate spreadsheets for different customers. So one spreadsheet will contain products and sales of only one customer. My intention is to define the start of the financial month of a customer manually through an input text icon and then pass the respective spreadsheet through the workflow to get the yearwise view.

Hope I am clear to u.

Thanks

danrh

I'd maybe look into using a formula to determine which fiscal year the record should belong to, then do a Summarize to get the total amount per year. Something like:

solution.yxmd

ayadav8

@danrh This is exactly what I wanted. Crisp and clean. Thanks so much!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703