Summarize Tool - finding total of users and total spent PER month

[soulton]

Hi,

I am trying to figure out the # of users who spent over a certain amount per month. I need to display the cardholder’s name, the total amount spent during the month, and the month for all who spent more than 10K per month. I am not sure why I am having such a hard time with this one. If anyone could review my workflow and assist I would be so appreciative! I am guessing it is something to do with the summarize tool to figure out the total, but the output I have been getting is not quite right.
(New here)

[Yoshiro_Fujimori]

Hi @soulton ,

I do not check all of your workflow, but here are some tips.

Workflow

Formula

"First day of the month" would be handy to group data with month.

Transaction Month =

DateTimeTrim(

  DateTimeParse([Transaction Date],"%m/%d/%Y"),

  "firstofmonth")

To format number to string, "ToString" function is useful.

Formatted Amount = "$" + ToString([Sum_Amount], 2, 1)

Output(amount spend during the month)

Output(spent more then 10K)

I hope this may be of some help. Good luck.

[DanielG]

@soulton  here is another attempt.  Please note on issue that was gumming it up as trying to group on the description field.  You arent likely to get too many like entries in what I presume is a free form text field.  I also fixed the date/time tools for you, as those were pulling through as null.  Got rid of some formulas that were doing what seemed to be unneeded formatting on the amount field.  I do like @Yoshiro_Fujimori using the FirstDayOfMonth.  Less tools needed to accomplish the same thing.  DateTime Tools can be a bit annoying because they create new fields and dont overwrite the originals.  Which can be good OR bad depending on what you are trying to accomplish.  😀 

[soulton]

Hi! Thank you. This helped immensely (I combined a bit of yours, mine, and Yoshiro's), however, I am trying to figure out a way to group the duplicate transactions made by a singular user within a month. Do you have any tips for this? Sorry to bother you.

[Yoshiro_Fujimori]

Hi @soulton ,

In that case, Sort tool may work.

Depending on how you want to group, you can change the sort order.

Workflow

Output

[soulton]

The sort tool did not work, but I was able to use the summarize tool so everything is resolved. Thanks!