Creating a Cumulative Concat Function on a Text Field
I currently have an ETL looking at distinct Opportunities by their close won day. However, this issue is now looking at Distinct accounts within the same dataset. At first, I thought to use the count distinct function on the account ID in a groupby. However, since multiple opps can be tied to one account and each one can have different close dates, I know this would cause the account ID to be counted multiple times instead of once. Could it be possible to instead concat the Account ID by the Opp close won date and have it be cumulative as a list format to where it would list previous accounts as well? From there, I can use a count distinct function, and if that account ID is shown more than once, then it won't be counted twice. I know cumulative functions can be done in the rank and window tile for INT values, but this is a bit different.
Best Answer
-
If I understand correctly, you want a count of Account IDs by close date, but if an account ID appears for more than one close date, you only want them counted for a single close date.
If that's correct, I would split this into two parts, first figure out which repetition of the Account ID you want to include, and then count them by close date.
For example, if you only want to use the most recent close date for a given account ID, use a rank tile to rank the account IDs by descending close date, using account ID as the window. Then a filter tile for rank=1 to get only the most recent Account ID. (Or maybe you only want to include the close date with the highest value, you could use that to rank it).
From there, you could count your Account ID by close date using a group-by.
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0
Answers
-
If I understand correctly, you want a count of Account IDs by close date, but if an account ID appears for more than one close date, you only want them counted for a single close date.
If that's correct, I would split this into two parts, first figure out which repetition of the Account ID you want to include, and then count them by close date.
For example, if you only want to use the most recent close date for a given account ID, use a rank tile to rank the account IDs by descending close date, using account ID as the window. Then a filter tile for rank=1 to get only the most recent Account ID. (Or maybe you only want to include the close date with the highest value, you could use that to rank it).
From there, you could count your Account ID by close date using a group-by.
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive