DOMO not calculating Weeknum correctly

user033540

I am fairly new to DOMO and below are couple of issues while working.

1. I was working on getting the Week number from Date Timestamp column. I could see that Date formula and Date function in ETL are considering date Timestamp from 4 AM.

For Eg :- Week 45 is from 12 AM of 1st Nov 2020 through 12 AM of 7th Nov 2020 but DOMO calculates Week 45 from 4 1st Nov 2020 4 AM through 8th Nov 2020 4 AM.

 

2. I am using DOMO workbench and pushing Excel file in DOMO server. For one of my column, DOMO is reading half of the cells as empty or null even if the cells has data.

 

Could you please assist me on this.

Thanks in advance!

 

GrantSmith

Hi @user033540 

You likely have a difference of 4 hours because Domo stores dates and timestamps as UTC under the hood and then converts the dates when displaying the data in a card. You need to make sure your data is in UTC time when ingesting it into Domo. You can utilize a Timestamp transform on a workbench job to convert your timestamp / date to let Domo know the timezone your data is in and it will automatically convert it to UTC when it's ingested.

 

As for your second issue - How have you configured your workbench job to handle error data? Are you able to look at your original file and compare it to the data in Domo to see if there are specific value that's causing the discrepancy? What type of data is in this column?

MarkSnodgrass

When Domo receives timestamp data, it assumes it is in UTC time and then applies the necessary timezone shift to match what your company settings are. You can see what time zone your instance is set to by going to More - Admin - Company Settings (assuming you have access to this section). 

If your Excel file is storing timestamps in your local time, you can shift them to UTC time very easily using Domo Workbench. In the Configure section of your job, go to Transforms and add the Shift Data Timezone Transform. This will apply every time the job runs and save you the trouble of doing anything in Excel. 

Once you have these in place, you should see the week numbers calculate as you expect.

Hope this helps.

user033540

Date timestamp in excel are in EST timezone and is static. I followed the workaround and shift the timezone to EST but still output remained same. Attached is the screen shot. 

MarkSnodgrass

It looks like you didn't complete adding the transform in the workbench. Once you select the Shift Data Timezone Transform, click the + sign next to it. This will open up a new window and have you select which timezone your data is currently in. Select the Eastern Time Zone and then click Apply. It should then be listed with a 1 next to it, if there are no other transforms in place. Run the job after this is in place and see how it looks.

user033540

Week number is the derived column from Date Timestamp in excel. I have looked into my original file and looks good to me. For error handling, I have used "Replace invalid cells with Null values".

user033540

Yes, I tried that but no good luck

user033540

I am still working on this issue. Can anyone please help or guide me to resolve this issue. If possible, we can connect over call also.

 

Thanks!

jaeW_at_Onyx

On the job board you can find freelance consultance who can support short one-off engagements like this request.  https://dojo.domo.com/t5/Job-Board/Freelance-Domo-consultant-available-for-hourly-and-long-term/m-p/48595#M44

 

In the immediate term, to troubleshoot:

take a dataset with 5 rows of data that you absolutely know have good dates, and then validate that your transforms work.  if they do... you know the problem (there's an error in your spreadsheet that your error handling isn't capturing).  if they don't ... you know the problem (your weeknum transform doesn't work as desired).

 

if you run into the former problem, type in the date insead of dateTime and work on permutations of that approach until you get it to work!

 

IMHO, you should apply weeknum in an ETL AFTER the data has been ingested into Domo b/c therer is business logic that must be applied to handle weeknumbers at the start and end of a year to avoid partial weeks.  doing it in ETL in Domo makes sense b/c it consolidates the transform logic.