Creating Buckets based on Month
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
Solved! Go to Solution.
- Labels:
- Developer Tools
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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')
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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, ...)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@rkeenan125 one way of doing this
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Ah ok, that makes sense. Yeah , that is correct unfortunately.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.