create a subset count of count summarised

[MyriamB]

Running User logins, I summarized count by user and by month, and with the count of log that populates, I would like to like within each month my user at scenario where log sum by user by month > 4 then count 1, else 0. and > 14 etc.....

How can I achieve ?

TXS

Myriam

[MSalvage]

@MyriamB,

If I am understanding what you are trying to achieve correctly, I think you should be able to insert a filter tool with the condition:

[SUM_logins] > 4

Then insert another Summarize tool grouping on Month and Count Distinct User ID. This should give you the number of users who logged in more than 4 times in the given month. Attached is an example with starting where I assume you left off.

Hope this helps,

MSalvage

[MyriamB]

Hello

Thank you so much @MSalvage

So with filters in my flow, it does not workit filters off results while I am trying to "mark" 1 or 0 rather based on criteria- I would need to add two new fields one for > 4 logs by user and by month, one for > 14, but I would like those as fields distinct from intial count- by user by month

per below I group by most fields in my initial pull so I do not loose any in output, and I have a count on login- When I use Group by on one file, can I assume that is a SUM? (u mention sum)

So if User 1 had 15 log in December, login count chose 15, > 4 and > 14 field I need to show "1". If Jan they have 5 logs, then > 4 as "1" and > 14 as

I hope I am making sense, really struggling to find the tool to do this.

Hopefully you have more idea?

TXS

Myriam

[MSalvage]

Hi @MyriamB,

Have you tried using a simple formula tool? Something like:

IF [SUM_Logs] > 4 THEN 1
ELSE 0
ENDIF

Find an Example attached. Is my assumed start correct do you have the data summarized to Number of Logins by user per month?

Best,

MSalvage

[MyriamB]

@MSalvage 

so I went to login data and created a field to return count of log, then used summarise the SUM the count of login, then added formulae

see below plus end results

added in flow won't return the expected results each log count is seen as < 1.

It is now midnight for me, am having to drop but I welcome more feedback, I feel stuck

[MSalvage · Accepted solution]

@MyriamB,

Okay I think I see the problem now. You need to group by month in the summary tool, but you don't have a month field. You can simply use the "DateTimeTrim" function in a formula tool to create this.

DateTimeTrim([LoginTime],'month')

Once you have the month you can group by "Month" (rather than LoginTime) and "UserId" and SUM the number of logins.

Attached is an example workflow that should help.

Best,

MSalvage

[MyriamB]

Dear M Salvage

That was the trick!

I pulled a sample report and getting the data correctly

Thank u so much

Myriam