Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

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

Creating Buckets based on Month

rkeenan125
7 - Meteor

Hi All,

 

My input data:

 

MerchantDateAmount
A2020-01-20$10.00
B2021-03-11$20.00
C2022-07-07$13.00
D2021-02-13$4.50
D2023-04-14$5.00

 

My output:

 

MerchantJanuary 2020Feb2020March2020...
ASum of amounts""""""
B""""""""
C""""""""
D""""""""

 

 

Ideally looking for the above output where it is grouped by Merchant and the following columns are months in the year for that last two years (25 columns including Merchant column.) And the values are the sums of the amount for the month per merchant across every month.

Any input is appreciated. Thanks!

8 REPLIES 8
cjaneczko
13 - Pulsar

Create a new field that formats the Month. Then Crosstab the data by grouping on Merchant, set the header to the new formatted date field and then Aggregate (Sum) on the amount.

 

DateTimeFormat(DateTimeParse([Date],'%Y-%m-%d'),'%h%Y')

 

rkeenan125
7 - Meteor

Thanks, I noticed this orders the column by month. Is there a way to format it to have them formatted chronologically in mass, without having to do it manually via Select? (Jan2020, Feb2020, ..., Jan2021, Feb2021, ...)

binuacs
21 - Polaris

@rkeenan125 one way of doing this

image.png

rkeenan125
7 - Meteor

This works! Thank you!. Might be a stretch, but is there a way to double click into the sum number, to see what makes up that sum, within the output excel doc?

binuacs
21 - Polaris

@rkeenan125 That is not possible with Alteryx, in that case you need to write excel sum formula using alteryx then write into the output file.

jdminton
12 - Quasar

@rkeenan125 as @binuacs mentioned, Alteryx does not work like an Excel pivot table. You can, however, use an analytic app to allow selection of specific merchants or dates to provide the detail. That gets a bit more complicated, though, so if this is to provide users with the details, you might want to have two workflows. One would do as above and the other would generate detail based on the criteria. My guess is that your data is either slow or too big for Excel. If that is not correct, let me know.

rkeenan125
7 - Meteor

Ah ok, that makes sense. Yeah , that is correct unfortunately.

jdminton
12 - Quasar

Since that is the case, you could also do something like use Alteryx to export the detail into an SQL database and query that in Excel using power query to allow the user to filter to the needed detail.

Labels
Top Solution Authors