MarkSnodgrass Coach

From a syntax perspective, you are missing an END statement. You only have one, but you need two because you have two CASE statements.

You can have your value field be a beast mode, which could be a calculation that would get your percentage. You might need to play around with a few calculations to see what is the correct combination, but it would be something like: COUNT(responses) / COUNT(location) You can then format the value field as percentage.

I would suggest adding a segment to show your total line. You can click on Add Dynamic Segment, select A, B, C. etc.., and assign a color for the line. Then drag the field into the segment field, which will become available after a segment is created. You can read the KB article about segments here:…

@lawiti you could add the date fields to your join criteria and then you could look for the nulls. You could also just join on the ID column and then use a filter tile after that and filter to where TableA date does not equal TableB date.

If you are only looking at the first character in a string you can use the LEFT function, like this: CASE WHEN LEFT(`orderstatus`,1) = '*' THEN 1 ELSE 0 END

If the * is always right next to SLD or SOLD then you could just tweak the beast mode to be like this: (CASE when `orderstatus` like '%*SLD%' THEN 'Sold' when `orderstatus` like '%*SOLD%' THEN 'Sold' ELSE 'Stock' END) If it can be anywhere in the string but also needs to contain SLD or SOLD then you could do this: (CASE…

You can use a case statement to evaluate this and look for SLD and SOLD. It would look like this: (CASE when `orderstatus` like '%SLD%' THEN 'Sold' when `orderstatus` like '%SOLD%' THEN 'Sold' ELSE 'Stock' END)

Column names are case-sensitive when Domo evaluates if a column is the same or not. For example, ID is different than id for Domo. You can use the Select Columns tile in Magic ETL to rename columns and keep your cards from breaking. Hope this helps.

Unfortunately, there are no sorting options for quick filters. It treats everything as strings and shows them in descending order. You would need to have the year first followed by the week number to avoid having all of the week ones grouped together, but you will still be stuck with it being in ascending order. Quick…

If you set your date range to the last 3 months on your card, you could use a beast mode like this: COUNT(DISTINCT CONCAT(`Customerid`,'-',LAST_DAY(`transactiontime`))) You would then filter to where the count equals 3.

Try this in a beast mode DATE_FORMAT(`yourdatefield`,'Week-%U %X')

You shouldn't need to format your date as you can use the date range filter and choose to graph by week. You can then put your date column in the sorting properties and sort descending. It should display like this.

You can use the United States map under the Maps Chart Type for this. https://domohelp.domo.com/hc/en-us/articles/360043429033-Country-Map#4.30. You can then show the sales by state and use the quick filter to show your list of vendors and then filter by vendor. If you clicked on a state, you could create a drill path that…

You can do this with a horizontal bar chart. You will need to use zero as your center and turn one series negative. Use another beast mode in your sorting that would take the positive sum of each category to get it sort like a tornado.

Did you copy and paste what I gave you? My formula validates Your latest examples have multiple errors with missing an END statement and incorrect quotes. Try just copying and pasting what I gave you the first time.

You can re-write your beast mode like this to make it work (CASE when `Date` >= '1/1/2022' AND `Date` <= '1/31/2022' THEN 1 ELSE 0 END) As currently, constructed, you will have to go back into this card once 2023 and update it. You may want to think about using the MONTH() function to determine the month and then use the…

That is strange. You may want to check your date range filter and make sure it is graphing by none. Also, sometimes I have to remove the sorting and re-add it. Or close the card and re-add it.

As I understood your question, you wanted to find out which customers ordered in one month and never again. This does that by having the invoice date in the count distinct clause.

I just used the COVID dataset as a test and it seemed to work. Here is how my pivot table is constructed. Here are my two beast mode functions: monthdate = MONTH(`date`) yeardate = YEAR(`date`) Is that how yours is constructed? You need the month and year as separate fields.

Have you tried using a pivot table card and creating a beast mode for the month using MONTH() and a beast mode for the year using YEAR() and put both of them in the columns section of the card and then your office in the rows section and the revenue in the value? That seems like that should work.

You are missing an END statement for your CASE statement COUNT(DISTINCT CASE WHEN `Account.Status__c` LIKE 'Cancel%' THEN 'Canceled' END)

Sorry, I missed that you had included InvoiceDate in the select statement and group by. That is why you are getting so many records. It should look like this: select Customer, Count(DISTINCT (CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,InvoiceDate)+1,0)) as DATE))) as OrderNum from ArTrnDetail where OrderType = 'ST' and…

Close. You need the count distinct like this: select Customer,InvoiceDate, Count(DISTINCT (CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,InvoiceDate)+1,0)) as DATE))) as OrderNum from ArTrnDetail where OrderType = 'ST' and ProductClass NOT IN('_FRT','_TAX','TAX2') and LineType <> '4' and Salesperson IN ('EPI','MGN','PH')…

try this, but replace GETDATE() with your actual date field: SELECT CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) as DATE)

Yes, you could use basically the same SQL statement I gave you. The only thing you should need to change would be to swap out the LAST_DAY function for the EOMonth function in MSSQL. Assuming the source data is the same in both places, you should get the same results by not changing anything else.

I noticed in your MSSQL example, you have some hard coded dates entered and you are using a dynamic date range in your MySQL example. Could that be why you are not seeing the same thing?

I think you might be able to do it by doing this: select Customer, count(distinct LAST_DAY(`InvoiceDate`)) as Purchases from `raw_syspro_artrndetail` where OrderType = 'ST' and ProductClass NOT IN('_FRT','_TAX','TAX2') and LineType NOT IN ('4') and Salesperson IN ('EPI','MGN','PH') Group By Customer having count(distinct…

Not sure if you have already seen this here: https://domohelp.domo.com/hc/en-us/articles/360044289573-Supported-Functions-in-the-New-Magic-ETL Function Name Description Example ANOVAF Returns the F-value of a one-way Analysis of Variance of the samples. At least two samples must be provided, and each sample must have at…

One of my instances is acting slow. Another is not. This page is worth checking to see if anything is going on, which there is:

The easiest way to do this is to use the Group By tile in Magic ETL. Add all your columns in the select except for the last_updated column. Add that one to the aggregated column list and choose Max. This will give you the most recent date for each.