Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

create a subset count of count summarised

MyriamB
7 - Meteor

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

6 REPLIES 6
MSalvage
11 - Bolide

@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
7 - Meteor

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

 

My flow.JPG

MSalvage
11 - Bolide

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
7 - Meteor

@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 midnightMy flow.JPGsummary.JPGmy sum.JPG for me, am having to drop but I welcome more feedback, I feel stuck

MSalvage
11 - Bolide

@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
7 - Meteor

Dear M Salvage

 

That was the trick!

I pulled a sample report and getting the data correctly

Thank u so much

Myriam

Labels