Best Of
Re: Trying to Count the number of Appointments in my data
@mhouston Bingo. Using AND not OR, and using an equal instead of like both fixed the issue and corrected the count. Thank you both @Billobi and @mhouston for helping!
Cwal
Re: Trying to Count the number of Appointments in my data
@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:
COUNT(DISTINCT
CASE WHEN concat(`Appt Text`, `Bay Code`, `Bay Text`)
<> 'No Code0NoBay' and concat(`Appt Text`, `Bay Code`, `Bay Text`) <> 'No Code0Bay 0' then `Invoice #`
end)
mhouston
Re: Stats for number of Cards per Page
Thank you @MichelleH and @mhouston You both answered my question. I as able to get the data from Domo Governance and then join the datasets to get what I needed. Thanks for your help
AttuAk
Re: Trying to Count the number of Appointments in my data
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)
Billobi
Re: Need help with beast modes to show yesterday's invoice total amount, and same day last year
Watch out for your second portion of the evaluation...easy typo but you're looking at YEAR() vs MONTH() so it's not going to yield any results.
Same day last year: case when year(`INVOICE_DATE`)=YEAR(CURDATE()) -1 and YEAR(INVOICE_DATE) = MONTH(CURDATE()) then INVOICE_TOTAL_AMOUNT
^^^ instead of year(invoice_date) I think you'd want month(invoice_date)
Your formulas don't really sync up with how you're describing the need. The "yesterday" beast shown is more like "current month to-date, including today". Yesterday would be something along the lines of case when INVOICE_DATE=CURDATE()-1 then INVOICE_TOTAL_AMOUNT.
When talking about yesterday year over year you can do it many ways. Keep leap years in mind so you're ready for 2024. Using DAYOFYEAR() is really easy and will work fine till 2024, then it's not a perfect calendar day match for most months. Or could use something along the lines of WHEN DATE_SUB(CURDATE(),INTERVAL 366 DAY) = INVOICE_DATE THEN INVOICE_TOTAL_AMOUNT. Or something that looks at DAY/MONTH/YEAR-1 matches. So many ways... :)
Billobi
Re: Stats for number of Cards per Page
@AttuAk i think you can do this but you will need a couple of the governance datasets. For page name, owner, created date - you can get this from the DomoStats Pages Report.
For number of cards - you can either use the pages report in the third party Domo governance connector or you could use the Domostats or you can use the Card Pages from DomoStats and do a group by on page id, coutn card id, and join to the DomoStats Pages dataset.
For created date of page - I think you can only get this from the pages report in the third party Domo governance connector.
For last date of data refresh - I think this is the most challenging. I did not see this in any of the page datasets - I think you would have to use the Cards and datasets report from the third party governance connector to get the dataset name/id, and then join to the datasets report from the third party connector to get the last updated date for the dataset - then you'd group on all the cards on the page to get the max last updated date (if you're using multiple datasets to power yours cards on the page).
mhouston
Re: Stats for number of Cards per Page
@AttuAk Check out the Domo Governance connector. There are several different reports you can use to access this information.
Re: Show a card ONLY when a specific dashboard filter is in use
One other thought - if the filter values are available within the dataset that the card would be built from, you could write a beast mode around the card metric(s) that would only display data when the filter criteria had been met. This can be computationally intensive and maybe a bit tricky to work out the correct logic based on your data, but it can definitely be done. This would mean the card would show no data until the case statement logic that would cause the metric to display was met, so it would still exist on the dashboard just with no data. It's probably not exactly what you want, but it could be a workaround.
