Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Income & Balance Sheet - Pivot table

Abegail0203
8 - Asteroid

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? 

 

 

 

 

12 REPLIES 12
MeganDibble
Alteryx Community Team
Alteryx Community Team

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.

 

Screen Shot 2022-08-05 at 9.44.35 AM.png

 

Hope this helps!

Megan

Abegail0203
8 - Asteroid

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.

 

Abegail0203_0-1659792020052.png

 

Amol_Telore
11 - Bolide

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?

Amol_Telore_0-1659814394463.png

 

Amol_Telore_1-1659814421501.png

 

Abegail0203
8 - Asteroid

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
8 - Asteroid

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:

 

Abegail0203_0-1659855252679.png

 

Amol_Telore
11 - Bolide

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
8 - Asteroid

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
11 - Bolide

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
8 - Asteroid

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

Labels