Creating Buckets based on Month

[rkeenan125]

Hi All,

My input data:

Merchant	Date	Amount
A	2020-01-20	$10.00
B	2021-03-11	$20.00
C	2022-07-07	$13.00
D	2021-02-13	$4.50
D	2023-04-14	$5.00

My output:

Merchant	January 2020	Feb2020	March2020	...
A	Sum 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!

[cjaneczko]

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]

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 · Accepted solution]

@rkeenan125 one way of doing this

[rkeenan125]

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]

@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]

@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]

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

[jdminton]

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.