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

Create pivot table with multiple group by and sort by functions

paulschindler
5 - Atom

I am trying to recreate a pivot table in Alteryx where I can group by multiple fields as well as sort by different parameters in each of those fields (i.e. A-Z, ascending, descending, etc).

 

As seen within the desired output screenshot, I am grouping the data first by Sale Month (A-Z), then Sales Rep (Descending Sales Price), and lastly by product (Descending Sales Price).

 

The values I am calculating are the Count of the Products and the Sum of the Sales Price.

 

Within Alteryx, I understand the Summarize tool fairly well but am a little shaky on the Cross Tab and Transpose tools.

 

Desired Output:

paulschindler_0-1639170847631.png

 

Data:

paulschindler_1-1639170872042.png

 

 

 

 

8 REPLIES 8
Ar13f
10 - Fireball

Dear @paulschindler 

 

You should know that I'm a rookie in Alteryx as well, but I'll do my best to assist you with your problem.

Perhaps another community will come up with a better solution later.

 

please find attached the workflow.

binuacs
20 - Arcturus

@paulschindler 

 

Spoiler
binuacs_0-1639396272486.png

 

paulschindler
5 - Atom

binuacs, really appreciate your solution here!  This works well.

 

For anyone else reading and interested in this, I updated the workflow to include a intermediate layer group of the subtotal by each sale rep in a specific month.  Only trouble I am left with is ordering everything the same way it is in the original excel pivot.

 

As of now, I am stuck with the SaleRep+Month subtotals all coming before the SaleRep+Month+Product subtotals.  If binuacs or anyone else has a solve for this, that would be great too! (see attached workflow "Pivot.v3").

 

paulschindler_1-1639503448116.png

 

 

paulschindler
5 - Atom

Appreciate your response! Check out the reply below and my reply to that for a more complete solution if you are interested.

Ar13f
10 - Fireball

Dear @paulschindler 

 

I can't revise the workflow from @binuacs  but please find my workflow maybe you can find a solution.

 

Pivot.png

Ar13f
10 - Fireball

Dear @paulschindler 

 

maybe the report below is what you want? I changed a bit from the workflow given by @binuacs .

Pivot_1.png

 

 

 

please check the attachment I provided.

Ar13f
10 - Fireball

Dear @binuacs 

 

Please allow me to make a minor adjustment to your workflow according by @paulschindler request.

 

 

 

 

regards,

Arief

binuacs
20 - Arcturus

@Ar13f - please feel free to make any changes . appreciate your time and help

Labels