Trying to Count the number of Appointments in my data
I feel like this should be simple but I'm struggling with how to write the beast mode to where I get a whole number and not an average.
I'm trying to count the total number of appointments in my sales data. This is what I've written:
Count(`Bay Text`)/(COUNT(DISTINCT`Invoice #`))
I've attached a photo showing what the data looks like. Each block outlined in color marks a different invoice (see the Invoice # column). Our data brings over rows of data for each line item on an invoice. Each line item shows the appointment type (Appt Text), bay code (Bay Code), and bay name (Bay Text) for each invoice (this is copied for each line item on an invoice even though its one entry of data for each invoice).
Invoices marked 'No Code' in Appt Text, '0' in Bay Code, and 'No Bay' in Bay Text indicate that that invoice did not have an appointment.
Any help/guidance is appreciated.
ps: I should clarify that I am trying to do this at the Beast Mode level and not the ETL level due to the fact that this data set is used across 1000s of cards and removing duplicates with a group by tile would effect many other cards and I'm not trying to create a separate output dataset for this one card.
So if "Invoices marked 'No Code' in Appt Text, '0' in Bay Code, and 'No Bay' in Bay Text indicate that that invoice did not have an appointment", does that mean that all other invoices have an appointment? If so I'd just do an evaluation against those specific contents to give you a unique count of the 'Invoice #' field.
COUNT(DISTINCT CASE WHEN CONCAT('Appt Text','Bay Code','Bay Text') NOT LIKE 'No Code0No Bay' then 'Invoice #' end)
(you could customize that concat however you like or add combinations to exclude other attributes that would indicate no appointment)2
@Cwal it looks like the problem is in the SQL syntax. I think I understand you are trying to exclude both values 'No Code0Bay 0' and 'No Code0NoBay'? So your SQL should be AND, not OR.
Also a side question - why are you using the like operator without any wild cards? I think you could just use an equal.
I'd rewrite your beast mode:
CASE WHEN concat(`Appt Text`, `Bay Code`, `Bay Text`)
<> 'No Code0NoBay' and concat(`Appt Text`, `Bay Code`, `Bay Text`) <> 'No Code0Bay 0' then `Invoice #`
This does work but by using this, I actually found that one of our locations has their bay text defaulted to 'BAY 0' instead of 'No Bay' like the other locations.
so I added this line to the beastmode:
But this returns data for this one store in question that looks like this:
when I remove
not like 'No Code0No Bay'
not like 'No Code0BAY 0'
in it's place, the data looks accurate:
Any idea why? Or what I'm doing wrong? Thanks for the help!0
- 7.6K All Categories
- 913 Connectors
- 241 Workbench
- 470 Transform
- 1.8K Magic ETL
- 60 SQL DataFlows
- 445 Datasets
- 24 Visualize
- 194 Beast Mode
- 2K Charting
- 6 Variables
- 14 Cards, Dashboards, Stories
- 1 Automate
- 348 APIs & Domo Developer
- 82 Apps
- 14 Predict
- 3 Jupyter Workspaces
- 11 R & Python Tiles
- 241 Distribute
- 59 Domo Everywhere
- 241 Scheduled Reports
- 14 Manage
- 35 Governance & Security
- 18 Product Ideas
- 1.1K Ideas Exchange
- Community Forums
- 15 Getting Started
- 1 Community Member Introductions
- 49 Community News
- 18 Event Recordings
- 579 日本支部