Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Auto-generate multi-tab P&L report based on each change in a set of fields

Carluccio555
9 - Comet

I have my data laid out per the input below and in the Excel file attached, but I'm struggling with the reporting side a little.

 

How can I auto-generate a P&L where each change in the 'Country' field is a separate tab, and Each change in the 'P&L', 'Line Item' and 'Detail' fields generates a subtotal calculation. To round off, a Full year column total and EBITDA calculation in the last row and column. In my example all the black numbers are data and the blue numbers are calculations.

 

My dummy data is structured very 'evenly' but I'd like this to work for any number of changes in those fields.

 

The formatting would be the cherry on top but I can explore that separately.

 

Many Thanks in advance!

 

Input:

 

Carluccio555_0-1668779784641.png

 

Output:

Carluccio555_1-1668779822637.png

Carluccio555_2-1668779852901.png

Carluccio555_3-1668779872363.png

 

 

 

 

 

8 REPLIES 8
gabrielvilella
14 - Magnetar

You can use the country field on the output data tool to create those multiple sheets.

gabrielvilella_0-1668780711470.png

 

Carluccio555
9 - Comet

Thanks, thats the easy part!

gabrielvilella
14 - Magnetar

No problem! If that solution worked, please don’t forget mark it as the accepted solution. 

Carluccio555
9 - Comet

Thanks, I'm going to wait to see if anyone can answer the much trickier part of creating the dynamic subtotals, hopefully someone can answer

gabrielvilella
14 - Magnetar

I haven't noticed you wanted that as well. Here is one way to do this. 

gabrielvilella_0-1668808260138.png

 

Carluccio555
9 - Comet

Hey that's great, love what you did with the record ID, thank you for teaching me.

Carluccio555
9 - Comet

 

How can I get the total labels in the 'P&L' and 'Line Item' fields to populate the nulls in the 'Detail' field so everything is in one column?

 

Thanks again!

Carluccio555_0-1668819850118.png

 

gabrielvilella
14 - Magnetar

Maybe this extra formula.

Labels