Summarize column dynamically

[sumanchinnam]

We have two files with monthly revenue data and with same layout. We wanted to compare current period month revenue from both the files. We build workflow by statically selecting the column that we want to summarize and then compare the values from both the file. But the issue is the files will be generated every month and the revenue bleed shows from the current period on wards. We want to automate the period to summarize based on the current month. Please see attached sample file. Just to explain the issue in detail. We want to sum the June period revenue by sales order line id and wanted to do same for next period that is July automatically without updating the period in summarize tool.

[john_miller9]

@sumanchinnam 

One way to do this would be to transpose the data first.  Then you can group by the columns you need to and summarize accordingly.  Finally, cross-tab the data to return it to the original format. 

If you post additional files (the second one you mention, along with the desired output format) we can show you what that workflow would look like.

Edit - take a look at this workflow.  That should give you an idea of how to approach this

[sumanchinnam]

John,

Thank you for your suggestion. Please find attached second file. I understand your solution but still how do i get difference of periods dynamically for example the files i provided i wanted to June period comparison for this month but for July period again i have to update period in formula tool to compare. I want to avoid editing the workflow every period and wanted to automate it.

Regards,
Suman

[john_miller9]

Thanks for providing the additional file.  However, it still isn't clear exactly what you want to compare?  Are you looking for the difference in values for the same files  in the two files? Or are you looking to sum the values from the same fields in the two files? Are you looking for a month over month difference Or something else? 

[sumanchinnam]

I am trying to compare the same fields JUN_19 from both the files and similarly next month i want to compare JULY_19 period amounts between files and the files will be generated next month starts with JULY_19 period. I hope this clarifies your question.

Regards,
Suman

[john_miller9]

Ok - this should accomplish that.  If you need it flattened like the original format, just cross-tab the file back to original layout.  Also, if you're doing this monthly you may want to think about creating a macro to dynamically select the two most recent files from the directory so you don't have to re-point the file inputs each month for the comparison.

[sumanchinnam]

Hi John,

It is comparing every month on the file but i just want to compare June period only for this month and from next month again i get new files which starts with July period. Please see attached workflow i already built. In the summarization config i am doing sum for June period. My question is when i load files next month i want to sum automatically on July period instead of i go to the summarization tool and update the field to sum. 

Regards,
suman

[john_miller9]

I'm unable to see your output as the source files aren't included in the workflow (you would need to export the workflow from the options menu), but I think all you would need to do is keep the first month (in this case, June) that appears in the first file, correct? 

If there would only be one record, you could simply use the sample tool. I used a tile tool in the event that you have multiple records for each month (e.g. representing each Order ID).  Let me know if that works.  If not, please post a file or image of the desired output.

[tariqhussain · Accepted solution]

I think I was able to understand your problem statement. Here is my solution.

I have created a new variable to get current month date and year - you can do this by calculating today's date and then formatting it to whatever shape you want it to be in. Then use this variable to filter out the rows for current month dynamically.

Workflow attached.

[sumanchinnam]

Tariq,

Thank for your solution. It resolved my issue. I also validated with file starting from next period and transpose tool is automatically selecting the columns has only values. 

Regards,

Suman