Best Of

Re: How to find the difference between 2 dates?

Another option would be to define a custom date offset dimension for the current date, last month and 2 months ago. You can then conditionally calculate the rolling average over the past 3 months or look at the difference between months based on the offsets your define. I’ve done a write up about offset dimensions before: https://dojo.domo.com/discussion/comment/50540#Comment_50540

@jaeW_at_Onyx has done a video on this as well. https://www.youtube.com/watch?v=CDKNOmKClms&t=325s

GrantSmith

1

Re: Can we do unpivot functionality in ETL, in the beast mode?

Hi @Salmas,

This isn't possible with a beast mode as it's performed on a row by row basis and doesn't have the ability to add rows to your dataset. You'd need to do it with an ETL.

GrantSmith

2

Re: Is this doable? Seeing percent of total day sales by hour using Timestamp column.

Hi @DANIH

Have you tried a window function with a case statement since you're attempting to only look at data from today?

Last Year:

SUM(SUM(CASE WHEN DATE(`Timestamp`) = CURRENT_DATE - INTERVAL '1' YEAR THEN `Net Sales` END)) OVER (PARTITION BY HOUR(`Timestamp`))

Last Year (With day of week aligned)

SUM(SUM(CASE WHEN DATE(`Timestamp`) = CURRENT_DATE - INTERVAL '364' DAY THEN `Net Sales` END)) OVER (PARTITION BY HOUR(`Timestamp`))

Yesterday:

SUM(SUM(CASE WHEN DATE(`Timestamp`) = CURRENT_DATE - INTERVAL '1' DAY THEN `Net Sales` END)) OVER (PARTITION BY HOUR(`Timestamp`))

Today:

SUM(SUM(CASE WHEN DATE(`Timestamp`) = CURRENT_DATE THEN `Net Sales` END)) OVER (PARTITION BY HOUR(`Timestamp`))

GrantSmith

1

Re: Is this doable? Seeing percent of total day sales by hour using Timestamp column.

@DANIH Do you have some sample anonymized data I could play with?

GrantSmith

1

Re: Converting Time zone with day light saving

Hi @User2021

Instead of CST I'd recommend using the country/city name like this:

CONVERT_TZ(`DateTimecolumn`, 'UTC', 'America/Chicago')

You can use this in either a Beast Mode on a Card or a Formula Tile in a MagicETL 2.0 dataflow.

Here's a list of timezone strings:

https://timezonedb.com/time-zones

GrantSmith

3

Re: Converting Time zone with day light saving

How is it not working? What are you getting? What are you expecting? Domo will convert your TIMESTAMP fields when a card is displayed to whatever time zone your instance is configured to.

GrantSmith

1

Re: Historic Gas Price Datasets

I'm not aware of one that's broken out per state in a single dataset however some states websites may have this information. You could also search through data.gov which has some state level datasets. https://catalog.data.gov/dataset

GrantSmith

1

Re: Row Limit in Bar Chart

Agree with @GrantSmith Your sorting and limit properties should look something like this:

MarkSnodgrass

1

Re: Row Limit in Bar Chart

Hi @micheleb

Are you sorting your results on your card based on the the aggregated / SUM of your values?

GrantSmith

2

Re: Is there a way to retrieve or export cards data to csv using Domo API?

Make sure you're using the correct layout id. Also you may need to do a PUT request to the writelock endpoint (https://[INSTANCE].domo.com/api/content/v3/pages/layouts/[LAYOUT_ID]/writelock ) to establish a write lock before attempting to save your layout and then issue a DELETE request to the same writelock endpoint to release the writelock.

The JSON structure that is sent in the PUT request to save the actual layout is slightly different than what you get back from the GET request. You'll need to monitor network traffic to get the exact structure needed (right-click in the browser, select inspect element, then select the network tab. Click Save on the layout to see what request is being sent)

GrantSmith

2