Struggling to create a filter for a chart - Email data (open+click)

Hi there,

I have a dataset and for each row I have an email id and a corresponding event (delivered, open, click etc).
So, I can have multiple rows with the same email id, but different events.

I’m creating some analysis on email ids that have only have an open and click event. So I need to create a filter for a chart which shows only this type of data.

I have tried the below, but it’s not working on the chart as I thought.

CategoryNum = ifelse(event = ‘click’, 2, event = ‘open’, 1, NULL)
OpenClick = distinctCountOver(CategoryNum, [EmailID], PRE_FILTER)
Then filter where openclick = 2

One of the charts is Month Email Sent on x axis, and distinctcount of Email IDs on Y axis.

Thanks,
Ben

@BCoole you need to convert your events into features first (transpose events), so you have 1 row/email.
Then you can create a calculated field to do whatever.

This will make other stuff much easier.

1 Like

@nshah-quicksight Hey, thanks for the advice. That would require changing the dataset for this one specific use case though?
Is there a way around with the data as it is?

@BCoole Nope.

<<><>>><>>

1 Like