Dynamic Year wise view

[ayadav8]

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!

[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

[cmcclellan]

Yes. if you've only got a few customers, I'd be creating for each customer:

- a Text Input tool (containing only client name and year end date .... no need for the start date)

- the real data file

- Append them together

repeat as required for each customer.

- union it all together, then use a DateTimeDiff to see if the date is in the last year and assign to that .... or some more calculations if you need to do multiple years.

[ayadav8]

@cmcclellan 

I tried creating your idea but still I am missing the logic to dynamically add the values and produce the year wise view. Attaching the workflow and input file. It will be great if you could help me understand the logic. This workflow is only for one customer with various product items.

Thanks 

[danrh · Accepted solution]

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:

[ayadav8]

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