What is best practice to Aggregate count of record based on a field

[MyriamB]

Hi

I load login users info and I would like from this pull to set up new fields that will group the data by month by user, counting the login time- while going thru other examples and options I cannot get to best option- I would have gone thru formula but would someone have some similar to start with?

TXS

Myriam

[LordNeilLord]

Hi Myriam,

You require the summarize tool....https://help.alteryx.com/9.5/Summarize.htm

[MyriamB]

Hello

Thank you for this first feedback- I already looked up the summary part but it does not provide me the exact use case

let me share some visual of what I extract. and the out put needed, knowing that eventually the By user info will need to aggregate under on line of business and show a by line business result for less 4 log and less 14.

[MyriamB]

Hello

Not sure I responded to you correctly- Could you check my follow up reply?

TXS

[JoeS · Accepted solution]

Hi @MyriamB

I believe you will need to use the Formula tool in order to create a month column. A formula like the below will give you the 3 letter abbreviated month name of a date column:

DateTimeFormat([Field1],"%b")

Once you have done that you can then use the summarize tool to aggregate your data. Grouping by both your "User Name" column and "Month" column, you then need to add a count into the summarize.

This will give you the data you need. If you then want to add some colour to the display you can look to use the table tool and set up rules to colour as required.

Or if you just want to use a flag, you can use the Formula tool again to create fields with the expression required.

[MyriamB]

Hello

Thank you, I had done month part and added the User name grouping- my initial flow had many other fields and the output I get with this flow is only the summarized ones, so now looking at jopigning this with the rest, unless there is another way?

txs

myriam

[MyriamB]

@JoeS

so this is my flow in pic. 1, and I added Summary after my Join from multi source data- all fields in that join I still need in output- If I run the flow as is below, I get pic. 2, which is only my group field count/ How can I make sure the other filed in previous step also carry over?

[JoeS · Accepted solution]

Hi @MyriamB,

Are the other fields you require unique at the same level as the User Name? 

If so you can group by them as well and that will keep the field.

Otherwise you can take the first value in the summarize or if that wouldn't work could you explain why they aren't unique and let me know what you would like to do?