cwolman Contributor

Some suggestions: Could there be trailing spaces in transaction_type? You could try `transaction_type` like 'Actuals%'. Also verify the case of transaction_type.

replace(replace(replace(`columname`,'[',''),']',''),'"','') This should replace [ ] and " accordingly.

One possibility would be to create a list of inactive employees and join this to the domo stats data which would allow you to easily exclude those employees.

Another option would be to keep the file in a cloud service such as one drive, google drive, dropbox, etc. Domo has pre-built connectors to these services and you could schedule how often the dataset should look at the file for updates. Sharing this file with the user would allow them to edit the file accordingly and the…

Sorry about that - I rounded up. Here is the formula for rounding down. sec_to_time((time_to_sec(time(convert_tz(`date_time`,'utc','us/pacific'))) div 1800)*1800) as `rounded_down`

No problem. us/pacific Enjoy!

Ahh, time zone conversion in Domo. I have lost many hours on this issue in the past. It looks like you are in mountain time so I will use that for my example. select `date_time` as orig_value , date(convert_tz(`date_time`,'utc','us/mountain')) as `date` , time(convert_tz(`date_time`,'utc','us/mountain')) as `time` ,…

Yes. Actually it is even easier because you do not need to use the str_to_date() function. The sql would be as follows replacing the `test` field with your field: select `test` as orig_value , date(`test`) as `date` , time(`test`) as `time` , sec_to_time(floor((time_to_sec(time(`test`))+900)/1800)*1800) as `rounded_time`…

No worries John. If i understand correctly the problem is that the User Start Date column is not reflecting the correct date because employees are not actually starting on the date provided by management. I am assuming that you are looking for an automatic way to determine the "true" start date of an employee based on the…

Hi John, You will need to update the sql to use the shift start date column instead of the start date column and we can use the date() function to convert the datetime to a date.

Hello, I worked under the assumption that your datetime column is a string and utilized the str_to_date function to convert the value. Hopefully this example will be helpful.

Hi John, I was able to accomplish this request by using two transforms. The first transform calculates the training_end_date by adding more days when required to account for Saturday and Sunday. The second excludes the dates between the start_date and training_end_date if they are a Saturday or Sunday.

Null value in either `Activity Date` or `Score` could cause a blank result. That would be the first place I would look.

Thanks for the recommendation. I have encountered this issue a few times and I have never been able to filter out those totals and was hoping that one of the much smarter people than me in here had a solution I never contemplated. No user has ever complained about it but I hate looking at a chart with a bunch of totals…

Hi Hari, Please refer to this link for information on using lastvalue replacement variable. Section number 19 has the specifics. cluster is the name of a column in my table that contains a unique value for each record. In the replacement variables tab is where you would put your field name and the starting value. Since my…

I am not sure how the table in domo can have a different collation but that is what the error is stating. Personally I have never seen this error but you may be able to run your query by using the BINARY operator. Even if this resolves you issue I would open a ticket with support to determine the root cause of the issue.…

The problem is that the date_format function returns a string value not a date value. As long as `call_date` is a date field then you should be able to compare to the curdate(). If `call_date` is text then you will need to convert it to date before doing date comparisons.

This is a shot in the dark so forgive me if you already explored this possibility. Does the workbench service run under another user account that could have an expired password that needs to be changed?

You would want to replace the nested if with a case statement. case when K5=0 and L5=0 then 'remaining' when K5=0 and L5>0 then 'resolved' when K5>0 and L5>0 then 'no gap' else 'new' end

Hi swagner, In order to do this you will need to use the concat function which will cause you to lose your summary number formatting. Re-formatting the summary number is where the beast modes become very large. The color and arrows can be accomplished by using this beast mode: concat( case when `Gap`>0 then '<div…

@Godiepi provided you with the beastmode and will work if all of the replacement values are consistent and limited. If the Sites are dynamically named or many then I would recommend a mysql dataflow with the sql below. I put your string in a table named data and named the column col_1 to keep the example shorter.

It appears that your job is expecting a parameter that is not being passed. Chris

Try this one out. convert_tz(`Date and time of Last Change`,'utc','us/central')

Yes, this was just a basic example that should work but does not. Below is a larger example that concats 2 measures that I want to display as the summary. If I concat the two fields it works fine. As soon as I put either measure inside of a case statement the summary number goes blank. concat( case when…

I would recommend running the historical data through a dataflow and utilizing the convert timezone function to convert the field to UTC.

Even a scheduled dataset will not run if the underlying data has not changed. The way I worked around the issue was to create a workbench job that runs daily at 6am which outputs the current date. My dataflow includes this dataset to kick off the dataflows I want to run at 6am daily.

Assuming that there are 0-20 possibly values you could make a MySQL dataflow like the following: 1st transform - named numbers select 0 as `no` union select 1 as `no` union select 2 as `no` union select 3 as `no` union select 4 as `no` union select 5 as `no` union select 6 as `no` union select 7 as `no` union select 8 as…

I have seen similar issues and the resolution in our scenario was to do the left joins on the data before bringing into Domo. You may want to look at exlcuding unneccessary columns and filtering the data down as much as possible. Good luck!

You will want to look at the Domo Governance Datasets. This is available in the app store via a connector. Do a search for Domo Governance Datasets and you should find it. There is a dataset available for Dataset Schema which will give you a nice format to create Domo reports or to export. Chris

You will probably find this to be a good starting point for the Domo DataSet API - https://developer.domo.com/docs/dataset/overview-5