Two trendline questions

clahonta

Hopefully someone can shed some light on two issues I am having. I am trying to compare hiring activity between four different functions, and am looking at the number of resumes reviewed per week. I have attached a screenshot of the card I am using, here are my questions:

 

 

1. The date labels are currently Excel format, but I want those to actually be Sunday-Saturday date ranges for the calendar weeks of the year labeled by date. Normally when I try to do WoW trendlines, the weeks are just labeled "Week 1 2020" or something like that. 

2. The third week represents a week we had a company-wide event that suspended our talent acquisition function for a week. Currently, the trendline is broken, but I would like to make it zero. How would I go about doing this? Thanks for the help!

ST_Superman

To convert the excel field to an actual date, you just need to create a beastmode using ADDDATE (or DATE_ADD ... they are the same function I believe).  The excel format is to count every day forward from January 1, 1900.  This means to convert it you need something like this:

DATE_ADD(STR_TO_DATE('1900/01/01','%Y/%m/%d'), interval `Excel Date Field` day)

This will give you a date field that you can pull into your card

jaeW_at_Onyx

Building on Superman's response,

From your screenshot it looks like you have a date that contains 'Offsite'.   which suggests you have text in what you want to be a date column.

 

when domo ingests data from excel it should autoconvert excel's integer representation of a date to ... date.  If the connector encounters text, the entire column would be stored in Domo as text (hence why your dates look like integers, but i suspect it's actually text.  Superman's Beast Mode might work, but it will fail on that text row.

 

SOLUTION:

Fix the data in Excel before ingesting the data into Domo

OR

Use ETL to fix the row and then convert it into Date. (you don't want to do this fix in Beast Mode because if you have to use this data in another card, you keep having to apply the same beast mode over and over).

 

With regards to adding a 0 row:

Most cards won't guess what you want the blank value to be.  With dates you might argue "just figure it out, i want one column for every date." but imagine if you said "just figure out all my customers, i want one row for all customers even if they had no activity."  You can't expect a card to just ... know all your customers.  same for dates.

 

SOLUTION

in ETL generate 'template data':   one row for each day with the metric set to 0.  that way, you have in your data each axis you'd like to see represented.

 

if the granularity of your data is one row per day per division or one row per day per customer AND you want to be able to filter on division or customer, you have to increase the granularity of your template data to match the axis you want to filter on (i.e. one row per customer per day with metric set to 0).

 

Hope that helps.

jae.wilson@domo.com