Report- Cross tab by month and separate to multiple files by name

[PassION_es]

Hi,

I am looking for a way to add in my report the cross-tab tool for effective dates but change them with the following headers: Jun 2022, July 2022, etc..

Desired output are multiple files segregated by the name of the staff with file names in the following format:

Filename: AKING INA SAMPLE 1 as of August 31 2022

Staff name & "as of" reporting month (August 31, 2022) because this month (September) has not closed yet.

Kindly note that column B in the output is always "CC".

In the input file there are two staff, so the output should be two files per staff. (all dummy snapshots and file)

[binuacs]

@PassION_es One way of doing this is with the batch macro. You need to pass the STAFF details in a separate file

[PassION_es]

Hello @binuacs . Your workflow runs fine however I just have some small findings and request for modification.

Here are the following:

1.) It did not have a "Total" Column as you can see it the snapshot output.

2.) The date in the filename should always be the last reporting month.  Correct should be August 31, 2022 because we are currently on September.

3.)In the output, "AKING INA Sample 1", I noticed a Jul 2022 header, instead of Jun 2022.  The batch macro output is correct - Jun 2022 (see below screenshot), however the excel output created has a header of Jul 2022.

I would want also to add a title report with "as of date" to be like the first snapshot ( SOA as of August 31 - which is the last reported month). Thank you.

[PassION_es]

hi @binuacs , to add on my first query above, I am getting this error when applied to the data i have:

batch macro:

Also, can we sort the order of columns in ascending order date? Nov 2021 is positioned at the end because it starts with letter "N".

[binuacs]

@PassION_es updated the workflow

[PassION_es]

Hello @binuacs , it seems almost all of my queries have been addressed. I tried to enter another set of data in your sample_input but unfortunately, the total inside the output file is not located in the last column: (see highlighted in red)

You can also see that May 2022 which is all in blank column is captured in the file eventhough there is no May 2022 transaction under that STAFF.

Attached is the same Sample_input file with the new data inside.

[binuacs]

@PassION_es I fixed the issues you mentioned above. 

I can see May 2022 has data in the spread sheet (5/5/2022)

[PassION_es]

hello Binuacs. I can see your modified data and each of the output is still capturing the unnecessary dates with all blank rows for some of the date column.  I am very sorry for this. For the nth time, can you check on the attached output please? The output files that were generated will be sent out via email to each of the staff (we have hundreds/thousands of staffs in our company)  and we want the automation to be presented in the best way we can (formatted table), see sample:

[binuacs]

@PassION_es no worries, you can ask any number of times, all am seeing as an opportunity to learn different ways to do things in Alteryx. The batch macro works very similar to the Union tool, that's why you are seeing all the fields in each STAFF even if they don't have any data in a particular month. I am looking for some other option to fix that issue. I will let you know once I found something. 

[PassION_es]

Thank you so much