Case When Aggregation compared to another Aggregation
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.
Best Answer
-
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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0
Answers
-
syntax is probably not correct, but would something like the below work?
case when closeddate - opendate <=4 then countdistinct(accountid) end
0 -
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.
0 -
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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
This will work. thank you
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive