MarkSnodgrass Coach

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.

@tejusarora Thanks! I tried that earlier and it was giving me an error, but now it is working as well.

If you're only wanting this to process for Account A, I would a nested case statement like this: (CASE WHEN TRIM(`column1`) = 'Account A' THEN (CASE WHEN TRIM(`column2`) = '' THEN `column1` ELSE `column2` END) ELSE `column1` END)

Blanks and nulls are not the same, so the ifnull function would not help with this data. I would try this to look for blanks: (CASE WHEN TRIM(`column2`) = '' THEN `column1` ELSE `column2` END) The trim function removes any spaces that may be in the data and then you can evaluate it to see if it is blank. Hope this helps.

Try just doing this: IFNULL(column2,column1)

Ahh... It was hard to tell from the screenshot if you need to access info from two different rows. In this case, You can still use the TIMEDIFF function, but you are going to need to do some work in Magic ETL first. You would need to utilize the rank and window tile and then use the Lag or Lead function so that you can add…

I would suggest looking at the TIMEDIFF function and the UNIX_TIMESTAMP function. The TIMEDIFF function is pretty straightforward: TIMEDIFF Returns the difference between values in two date/time columns, expressed as a time value. TIMEDIFF('Time 1','Time 2') The UNIX_TIMESTAMP function will require a little bit more work.…

You might also try: IFNULL(`company_name`, `group_name`)

You chose Heat Map for your chart type, which is actually different from the Heat Map Table. On the right side, choose Tables and Textboxes from the Chart Types dropdown list. Then choose Heatmap table for the chart type.

Within Analyzer, make sure Properties is selected. This will allow you to adjust the chart properties. You will then want to go to General and enter in which columns you want to apply the heatmap to as I referenced above.