custom group with filter

[rahul_mishra]

in cloud looker i want to create a chart from explore, my kpi is all events created by month and counts of events whose user have another events for e.g. a event has a user, the same user can have a another event, both events are created in 2 seperate months. i need tp count only those events having user in multiple events month wise.
How to create a chart from this explore where the user view is joined to event view. is there any way without creating a seperate derived table or view to achieve this.

[GavinW]

Depending on your use case and the volume of data involved, you can do this in the front end with table calculations, but you would be limited to 5k rows of data.

If you pivot your data on the month and use your

count of events as the metric and your user as the dimension, you will get a matrix of months that a specific user had an event. You can then use a table calculation to check for users with more than one event per month, as you can see below (I am using 'orders' rather than 'events' as my measure):

I am using this table calc to get the values from each row ('Months with Orders'): 

pivot_row(${orders.count})

And I am then checking if there is more than one month with an order in the 'More than one order month?' table calculation, which just looks like this:

if(count(${months_with_orders})>1, yes, no)

I can then hide the 'no' values in the table visualisation if I choose so I am left with a list of users that have more than one order in the last 6 months.

[rahul_mishra]

Thank you, but i need total count, not on individual id's

[GavinW]

Ah - that is also doable. Just change the 'More than one order month?' table calculation to return a '1'or a '0' instead of 'yes' or 'no'. Then you can create another table calculation that just counts the sum of that table calculation.

Note that you can combine a couple of these steps into a single table calculation if you like.