jhl Member

Valiant Spur, thanks for this - currently kicking myself because your explanation is so. much. more. elegant. than the (unecessary) rank and window stuff I described here. Even had the SQL in there, how did I forget about group by in ETL^^ JHL

Youre right, my mistake - you can ignore the rank and filtering (unless you have another column to set as the second ranking row). Instead, you can use ETL function "remove duplicates" as a last step before creating the output dataset to remove any duplicate dates you have - because the average will be the same in all…

Hi, sure,I'll try. 1) use "set column type" on your datetime column to make it a date (without a timestamp) 2) use "rank and window" for two functions: -Use "rank" (ascending) using the "add function" - you will have to give your column a name. - use "average" on your Value column (whatever you are actually measuring) -…

Hi, I think what you need is a window function - Beast Mode does not support it, unfortunately. However, Magic ETL does. (There is a chance that it works in SQL too using Select `Date`, AVG(Value) from source GROUP BY `Date` but I havent tested this) You will need to use your date as a partition - maybe you will need to…

Hi, if what I think you want to do is what you want to do (calculate number of minutes between two timestamps) then I do not entirely understand why you cant just delete the first *60 part to get the right result? You definitely cant use the same code for this. Two different query languages return different results when…

Hi, I am not sure what is going on with the join, the inner join adds only rows that have a matching identifier in both datasets, I dont know the definition by heart, but it might just be that it matches multiple times because it is a constant... 1k x 1k = 1m. But there is an easier way than matching on your additional…

Hi Greg_B, you might want to check out this link (especially example 3). What you will need to do is create a beast mode with CONCAT() and put a CASE WHEN statement into the HTML color rule, so something like CONCAT('<a style="color: , (CASE WHEN `Value` < 15 THEN '#f00' WHEN > 50 THEN '#00c200' ELSE '#ff0' END)…

Hi Bharti, I would assume (without knowing anything more about this, it actually sounds interesting, I figure this would be insightful for world cups etc...) you will need your input data as something like this (dummy) Date Vacation Hours Football Game 10/10/17 18 1 11/10/17 7 0 ... (third column could count football…

Hm... interesting, this works for me, is described here and here. Did you actually finish the installation process? If so, you should already be connected to your instance and should be able to create jobs.

I think this is just the address of your domo instance, namely yourcompanyname.domo.com HTH

Have you tried just multiplying the number by -1 using beast mode? This would mean that the shortest times are the "largest" words. Depending on your original data type you might have to try reformatting it to for example, a decimal field so it can actually by multiplied with anything (if it is just a decimal with, say,…

Hi mylee, sorry for not being entirely clear if you just set up one beast mode like case when `Country` LIKE '%Poland%' then 'Poland' when `Country` LIKE '%Sweden%' then 'Sweden' when `Country` LIKE '%France%' then 'France' when... then... end and put as many "when... then..." statements into the beast mode as you need,…

Hi Lee, you can try the LIKE operator in a beast mode calculation, such as case when `Country` LIKE '%Poland%' then 'Poland' end repeating the 'when... then' part for each country you have in your list (or may need) and then setting a filter on the calculated field. The '%' here serves as the placeholder to make sure it…

Hi Randy, as far as I understand, there may be a) an issue with the join (left joins keep all lines in the left dataset and add only matching lines on the right) but this may not be the case, just make sure the join doesnt drop all orders that do not have a survey related to it. b) given what you say here When I do the…

Hi, I have to admit I do not fully understand. With the information you gave us, for the incorrect rate I figure you have a timeframe specified (in a case when statement) in the conversion rate calculation. This would mean the beast mode calculation is affected by switching from monthly to weekly, because not all dates…

Hi, never encountered this before - but the solution I would try: -create a new graph from the dataset -go into colors: you should see Rules loaded from DataSet NAMEand you could just delete them, or replace them with "auto" and then choose "apply to dataset" and then confirm to remove all colors from the dataset…

Hi, you may want to check out this link to the knowledge base - there is a graph type called "grouped bar chart." You will have to put -date into "category" - apple sales into "value" - banana sales into "series" and the result will be something like the dummy data graph I have attached. HTH

I do not think this is possible using the existing color rules in analyzer. However, you might want to take a look at Adding Graphics, Links, and Images to Table Cards Using Beast Mode from the knowledge base. The same logic (using CONCAT in beast mode to insert html code) may apply and help you achieve what you want. HTH

Currently, this is not possible directly in cards. However, I believe this is on the roadmap for the future. You can contact your customer success manager for more details. You can also always suggest improvements in the Idea Exchange. HTH

Just a quick note: Your earlier iteration wasnt working (even for less than 30 days, but that would not have been obvious immediately) because of some quirks of SQL-based languages. Most of the date function take only one thing into account - in DAY() it is the day number of the month. This means if something is, say,…

Hi, that is an interesting question. As far as I understand, you essentially want to perform a calculation that would boil down to the definition of a window function (which beast mode does not support): performing calculations over different columns AND multiple rows at the same time. Beast mode does not allow for that…

Hi, I believe Domo does not recognize a string like '2017-11-05' as a date, but as text. You probably need to use STR_TO_DATE() around it - you can find the function here (scroll down a little). This way you could just exchange the DATE_FORMAT() part for STR_TO_DATE() with very little reformatting involved. This List of…

Always happy to help ?

I noticed that in some of our graphs it seems to be called "is always" now.

I just fiddled around with another dataset, try setting one of the color rules to "contains any" - this will get rid of the duplicate color and your labels apply.

Is there another color rule applied to the dataset? 

Do you have a color rule for Total Minutes late other than the >10 or <=10 minutes? If there is one that "contains any" in your color rules, it will display even if it is not in the chart. (the question is also why you would want this, judging from your chart everyone but two is late :p)

If I am understanding this correctly, you are not sure about the type of join you are using? Just FYI, you can specify the type of join to use in Magic ETL by clicking on the symbol between the two datasets you will see when editing the "Join Data" step.