Income & Balance Sheet - Pivot table

[Abegail0203]

Hi,

Requesting assistance on how alteryx works as a substitute for pivot (see attached).  This dummy file consist of:

Input data sets:

1st tab - GL balance (activity & ytd)

2nd tab - GL mapping 

Outputs:

3rd tab - balance sheet 

4th tab - income statement 

Can alteryx have the capability to group and sub group and add rows to sum the total per group/subgroup? 

[MeganDibble]

Hi @Abegail0203,

I took a stab at the balance sheet, using a join and summarize tool. Workflow is attached as a package--I just did three of the month columns. Hopefully, you can use that as a template for the income sheet process as well.

Hope this helps!

Megan

[Abegail0203]

hi @MeganDibble ,thanks for taking time to check my topic.  I already know how to create a workflow with the output you provided.

What I need is how to have it presented the way it looks like in my file provided.  As you can see, there are totals in each of the type of GL account on the first and second column and a Grand Total at the bottom.

[Amol_Telore]

Hey @Abegail0203 

Yes. It is possible to generate report same as above formatting however, As you can see, we can apply filter in pivot table in excel which is not possible via Alteryx. Alteryx report is static report with no dynamic filters.

Below is the snapshot of the report we can generate using Alteryx. Let me know if this is what you are looking for?

[Abegail0203]

Yes @Amol_Telore , Amazing! this is what I am looking for.

Appreciate much if you can share with me your workflow? I understand also that Alteryx don't have the capability to filter this output just like refreshing the pivot.  Thank you.

[Abegail0203]

hi @Amol_Telore , in relation to the above report generated, i would also want to have a 3 columns for the current , prior month, as well as their increase and decrease.  In this case, the last reporting date is July, so can Alteryx pick up this as the current, while the prior will be June. and the difference between the two months will be the increase/decrease.  They may look like this, to be located after the POC column:

[Amol_Telore]

Hey @Abegail0203 

I am glad I was able to aid you in some way. However, I have a few questions with regards to your last comment.

1. Do you also need total and subtotal for these three columns?
2. Is it needed for the Balance Sheet, Income Sheet, or Both?

If you could update these three fields in the attached sheet, it will help me to understand your exact requirement along with the formatting required.

[Abegail0203]

hi @Amol_Telore , thank you so much for your time and patience.  Here's the updated one. Please note the balances are dummy only.

Also, I added two outputs (summary balance sheet and summary income statement) to complete the output in one spreadsheet.  I'm not sure if Alteryx can create those kind of formatting. Thank you.

[Amol_Telore]

Hey @Abegail0203 

How did you get those numbers in summary tabs. Also I see summary sheet has few additional GL grouping which is not present in the raw data. Could you please check and let me know. 

[Abegail0203]

hello @Amol_Telore , apologies. The 2 summary tabs  came from the details (totals of each group). Thank you.