Creating Buckets based on Month

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!

Developer Tools

Accepted answers

binu_acs

@rkeenan125 one way of doing this

Buckets_18042024.yxmd

All comments

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, ...)

binu_acs

@rkeenan125 one way of doing this

Buckets_18042024.yxmd

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?

binu_acs

@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 @binu_acs 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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703