help with logic - finding unique number of `column1` that have BOTH value '1' and '2' from `column2`

cjack-PML

I am struggling through working out the logic for this. Can anyone help me think through this? There might be an entirely different approach that is much easier.

 

I am sorting out an email campaign and I want to know how many unique `EmailAddress` show up for BOTH `EmailName` value 'email1' and 'email2' at least 1 time.

 

(To clarify, this is an open report I am working with, so a new row populates with `EmailName` for each open. so if a person opened 100 times, that would be 100 rows, same `EmailAddress`.)

 

I created new columns with integers for each of the emails. 

 

So, `Email1Column` is filled with:

CASE WHEN `EmailName` like '%Email 1%'

then 1 else 0 end

 

`Email2Column` has:

CASE WHEN `EmailName` like '%Email 2%'

then 1 else 0 end

 

I created a table that has `EmailAddress`, SUM Email1Column and SUM Email2Column as the columns.

 

I want to filter ONLY the rows that have a value greater than 0 for BOTH Email1Column AND Email2Column.

 

When I try to put in filters on the visualization, it is just empty (for the same reason I can't use an AND statement - the data is different rows, so >0 for one means excluding the other).

 

I feel like I am close here. Any help is appreciated.

GrantSmith

Hi @cjack-PML 

 

You can't filter on aggregate functions (unless you have an alpha feature enabled to do so but doesn't always work as intended).

What I'd recommend is using an ETL (or a DataView if you're in the beta) to calculate these numbers and then using the resulting dataset to graph where you can filter on the aggregated numbers.

cjack-PML

Thanks @GrantSmith for the response.

 

I did use an ETL to generate those two new columns.

 

Can you elaborate on what I should do? I am just hitting a wall on thinking this through. Do you mean I should create a column that only includes the greater than 0 rows for each? I suppose that would make sense.

GrantSmith

Hi @cjack-PML 

 

You'd need to do your grouping and aggregation within your ETL using a GROUP BY tile and creating two new fields (email 1 min and email 2 min). Then add both together using a calculator tile. If the new value is 2 then they both would exist and you can then use that new field to filter on in your cards.