Case When Aggregation compared to another Aggregation

jbandley

I am looking for a way to get a count of accounts that have had a note closed within 4 days of it being opened.  The issue I am having is when an account has a note that is closed within the 4 days but has had another note opened but is not closed within the 4 days within the same time period of the first note.

example

Account#       Noteid            OpenDate                     ClosedDate

4                     1459               1/15/2019                       1/16/2019

4                     1460               1/16/2019

I need to get both to not count toward the count of accounts closed within 4 days. I am looking for a beast mode because the report that is needed will need to be based on a date range in the card.

MarkSnodgrass

You can do this by adding an additional column in the ETL that you can use to Exclude the open accounts and still keep your main dataset intact to do other analysis. You would build an ETL like this: (I'll break down the steps after the screenshot)

SampleStatus - this is your initial dataset

Days to Close - This is a simple data calculator tile that finds the difference between the open and closed date fields

Rows with no close dates - Filters your dataset to only have rows where closed date is null

Select Columns - Removes all other columns except for the account number column

Removes Duplicates - makes it so we only have an account number listed once in this dataset

Add Exclude Column - Uses the Add Constant tile to create a column called Exclude with a value of Y

Join Data - Performs a Left Join with the Days to Close tile which has all of your rows and the Add Exclude Column tile and joins it on Account Number. The result is a Y in the Exclude column in all rows where there is a match of Account Numbers. 

Drop Extra Account # Column - Select Columns tile that removes the duplicate account number column as a result of the join

SampleStatusDF - Output Dataset

 

With this new dataset, you can drag the Exclude column into your quick filter and choose Not in Y to get rid of all account numbers that have an open note despite having a closed one earlier. 

You also have the DaysToClose column that you can use as a filter to only show those with a value of 4 or less.

KVincent

syntax is probably not correct, but would something like the below work?

 

case when closeddate - opendate <=4 then countdistinct(accountid) end 

jbandley

We have tried that. The issue still remains that is if the account has a note closed but has another note opened past 4 days, we need to count that account as having a note open even though the first note was closed within the 4 days.

 

 

MarkSnodgrass

You can do this by adding an additional column in the ETL that you can use to Exclude the open accounts and still keep your main dataset intact to do other analysis. You would build an ETL like this: (I'll break down the steps after the screenshot)

SampleStatus - this is your initial dataset

Days to Close - This is a simple data calculator tile that finds the difference between the open and closed date fields

Rows with no close dates - Filters your dataset to only have rows where closed date is null

Select Columns - Removes all other columns except for the account number column

Removes Duplicates - makes it so we only have an account number listed once in this dataset

Add Exclude Column - Uses the Add Constant tile to create a column called Exclude with a value of Y

Join Data - Performs a Left Join with the Days to Close tile which has all of your rows and the Add Exclude Column tile and joins it on Account Number. The result is a Y in the Exclude column in all rows where there is a match of Account Numbers. 

Drop Extra Account # Column - Select Columns tile that removes the duplicate account number column as a result of the join

SampleStatusDF - Output Dataset

 

With this new dataset, you can drag the Exclude column into your quick filter and choose Not in Y to get rid of all account numbers that have an open note despite having a closed one earlier. 

You also have the DaysToClose column that you can use as a filter to only show those with a value of 4 or less.

jbandley

This will work. thank you