Visual to Filter by CountOver

[riley.anderson]

I am trying to create a filter on my table that excludes records which have a count of distinct ‘_id’ of less than 10 grouped by email and date.

The visual is a pivot table which has
Row: syscreated (MONTH)
Column: Day of Month (calculated field extracting day from syscreated field)
Value: syscreator_email

Data:
_id
syscreated
syscreator_email

From my visual I want to add another filter which filters out any syscreator_emails for any particular day if they have less than 10 '_id’s for any given date.

I think I might need to use the countOver function in a calculated field but I haven’t managed to make it work yet.

Any further questions or issues and please just ask, many thanks.

Riley

[Max]

You can do this:

distinctCountOver({_id},[{syscreator_email},{syscreated}],PRE_AGG)

Then use this field as a filter and make it greater than or equal to 10.

[riley.anderson]

Hi @Max,

Thank you for your response, unfortunately, it is not working still.

Here are some screenshots to help explain/show:

Table without attempting to filter out these records

Calculated Field:

Athena query result for 5th June to show that of the 9 results, 2 emails have a value of less than 10 that I want to exclude from my Quicksight Table

Applying the filter still shows as 9 agents for 5th June.

Hopefully this helps explain and show the problem better. Any further help will be greatly appreciated, many thanks.

Riley

[riley.anderson]

Hi @Max,

Really sorry to bother but you haven’t had chance to have a look at my above to reply to see if anything is wrong have you?

I look forward to hearing from you.

Many thanks,
Riley

[Max]

Hi @riley.anderson

Are you saying you want this count grouped by the month? In that case you can truncdate the syscreated date.

distinctCountOver({_id},[{syscreator_email},truncDate('MM',{syscreated})],PRE_AGG)

Let me know if that works