Best Of

This one was a bit tricky and fun and I spent way too much time on this. I ended up doing this in a Magic ETL2.0 formula tile and building off of the different components. I'll break it all down below and at the bottom will be the ETL code you can copy and paste into the new Magic ETL which should have it all for you.

Notes:

• You'll need to update your fields in the Modified Start Date and Modified End Date formulas.
• This doesn't take into account holidays.
• Assumes M-F work week.
• This hasn't been fully vetted with different edge cases but the ones I have tested with seemed to come out correctly. Be sure to test this our yourself.

First step I just put in my hour constants for the start and end hours, in your case they're set to 7 and 20 (24 hour clock)

Modified Start Date (step 1):

GREATEST(`Start Time`,
  DATE(`Start Time`) + INTERVAL `Day Start Hour` HOUR,
  DATE(`Start Time` + INTERVAL (24 - `Day End Hour`) HOUR) + INTERVAL `Day Start Hour` HOUR)

Get the greatest timestamp of the start time being either

1. The actual timestamp
2. The business start of the day (converting timestamp to date makes the time midnight / 0:00, adding the day start hours give you the business start of the day)
3. If the start time is after the end of the day and goes into the next day start it on the next day at the business start time

Modified Start Date (step 2):

`Modified Start Time` + INTERVAL
	CASE WHEN DAYOFWEEK(`Modified Start Time`) = 1 THEN 1
  	WHEN DAYOFWEEK(`Modified Start Time`) = 7 THEN 2
  ELSE 0
  END DAY

If we've been pushed into a Sunday Add another day, Saturday add two days to the start date.

Modified End Date (step 1):

LEAST(`End Time`,
  DATE(`End Time`) + INTERVAL `Day End Hour` HOUR,
  DATE(`End Time` - INTERVAL `Day Start Hour` HOUR) + INTERVAL `Day End Hour` HOUR)

Get the earliest end time being either:

1. The actual timestamp
2. The end of the business day for the End Time (if it's at 11pm it'll bump it back to 10pm)
3. If the end time is before the start of the next day bump it back a day to the end of the day

Modified End Date (step 2):

`Modified End Time` - (
  		INTERVAL (CASE WHEN DAYOFWEEK(`End Time`) = 7 THEN 1
  		 	WHEN DAYOFWEEK(`End Time`) = 1 THEN 2
	 		 		ELSE 0
  				END) DAY)

If we've been pushed into a Sunday subtract another day, Saturday subtract two days.

First Day (business seconds elapsed in the first day)

  (UNIX_TIMESTAMP(LEAST(`Modified End Time`, DATE(`Modified Start Time`) + INTERVAL `Day End Hour` HOUR)) - (UNIX_TIMESTAMP(`Modified Start Time`))) -- First Day

Get the unix timestamp for the earliest time. If the End time is in the same day use that, otherwise use the end of the day for the start time.

Last Day (business seconds elapsed in the last day)

(UNIX_TIMESTAMP(`Modified End Time`) - UNIX_TIMESTAMP(GREATEST(`Modified Start Time`, DATE(`Modified End Time`) + INTERVAL `Day Start Hour` HOUR))) -- Last Day

Similar to the first day beast mode this will get the latest time between the actual timestamp and the start of the day of the end date and subtract that from the end timestamp

Middle Days

(GREATEST((DATEDIFF(`Modified End Time`, `Modified Start Time`) - 1), 0) * (`Day End Hour` - `Day Start Hour`)) * 60 * 60 -- Middle days in working seconds

Gets the number of days between the two dates and multiplies that by the number of hours in the working day to get the number of working seconds for the days in-between.

Weekends:

FLOOR(DATEDIFF(`Modified End Time`, `Modified Start Time`) / 7) * 2 * (`Day End Hour` - `Day End Hour`) * 60 * 60

Determine the number of days between the two dates and divide by 7 taking the FLOOR to return the number of whole weeks in between the two dates. We then multiply it by 2 (Saturday and Sunday) and convert it into the number of working seconds (working hours in a day * 60 minutes in an hour * 60 seconds in a minute)

Working Time:

SEC_TO_TIME(
  `First Day`
+
`Last Day`
  + `Middle Days`
  - `Weekend Days`
  )

Add the seconds from the first day, last day, middle days and remove the amount of working time on the weekends to get the total number of seconds. Pass that into SEC_TO_TIME to give us a readable HH:MM:SS formatted duration.

ETL Code:

Copy and paste the following to insert the formula tile into your New Magic ETL:

{"contentType":"domo/dataflow-actions","data":[{"name":"Add Formula","id":"dc1bc960-c2c4-40a5-b6d2-680b3a92a987","type":"ExpressionEvaluator","gui":{"x":408,"y":132,"color":null,"colorSource":null},"dependsOn":["c009fb72-5395-460a-8c3e-b37032557743"],"removeByDefault":false,"notes":[],"expressions":[{"expression":"7","fieldName":"Day Start Hour","settings":null},{"expression":"20","fieldName":"Day End Hour","settings":null},{"expression":"GREATEST(`Start Time`,\n DATE(`Start Time`) + INTERVAL `Day Start Hour` HOUR,\n DATE(`Start Time` + INTERVAL (24 - `Day End Hour`) HOUR) + INTERVAL `Day Start Hour` HOUR)","fieldName":"Modified Start Time","settings":null},{"fieldName":"Modified Start Time","expression":"`Modified Start Time` + INTERVAL\n\tCASE WHEN DAYOFWEEK(`Modified Start Time`) = 1 THEN 1\n \tWHEN DAYOFWEEK(`Modified Start Time`) = 7 THEN 2\n ELSE 0\n END DAY","settings":null},{"expression":"LEAST(`End Time`,\n DATE(`End Time`) + INTERVAL `Day End Hour` HOUR,\n DATE(`End Time` - INTERVAL `Day Start Hour` HOUR) + INTERVAL `Day End Hour` HOUR)","fieldName":"Modified End Time","settings":null},{"fieldName":"Modified End Time","expression":"`Modified End Time` - (\n \t\tINTERVAL (CASE WHEN DAYOFWEEK(`End Time`) = 7 THEN 1\n \t\t \tWHEN DAYOFWEEK(`End Time`) = 1 THEN 2\n\t \t\t \t\tELSE 0\n \t\t\t\tEND) DAY)","settings":null},{"expression":" (UNIX_TIMESTAMP(LEAST(`Modified End Time`, DATE(`Modified Start Time`) + INTERVAL `Day End Hour` HOUR)) - (UNIX_TIMESTAMP(`Modified Start Time`))) -- First Day","fieldName":"First Day","settings":null},{"expression":"(UNIX_TIMESTAMP(`Modified End Time`) - UNIX_TIMESTAMP(GREATEST(`Modified Start Time`, DATE(`Modified End Time`) + INTERVAL `Day Start Hour` HOUR))) -- Last Day\n","fieldName":"Last Day","settings":null},{"expression":"(GREATEST((DATEDIFF(`Modified End Time`, `Modified Start Time`) - 1), 0) * (`Day End Hour` - `Day Start Hour`)) * 60 * 60 -- Middle days in working seconds","fieldName":"Middle Days","settings":null},{"fieldName":"Weekend Days","expression":"FLOOR(DATEDIFF(`Modified End Time`, `Modified Start Time`) / 7) * 2 * (`Day End Hour` - `Day End Hour`) * 60 * 60","settings":null},{"expression":"SEC_TO_TIME(\n `First Day`\n+\n`Last Day`\n + `Middle Days`\n - `Weekend Days`\n )","fieldName":"Working Time","settings":null}]}]}

Did you copy and paste what I gave you? My formula validates

Your latest examples have multiple errors with missing an END statement and incorrect quotes. Try just copying and pasting what I gave you the first time.

@AJ2020 this is an interesting question.

are you asking for max columns you can read in via a connector? or maximum columns you can create in ETL?

I believe the max columns is 1024 but i'm probably wrong.

More importantly... why are you hitting the max number of columns? Could you shape the data in a way that's more useable? (particularly if you're working with a connector that adds columns based off of distinct values in a JSON array, you'll want to reconsider b/c from day to day the shape of the dataset could change).

i believe Domo support can lift an arbitrary limit ... but again, why would you have a multi-hundred-column dataset and more importantly could you use it in a way that makes sense?

Thanks @MarkSnodgrass !..Appreciate your help!

You can use the United States map under the Maps Chart Type for this.

https://domohelp.domo.com/hc/en-us/articles/360043429033-Country-Map#4.30.

You can then show the sales by state and use the quick filter to show your list of vendors and then filter by vendor.

If you clicked on a state, you could create a drill path that would then show you the vendors that had sales in that state.

That worked, thanks @MarkSnodgrass

@Joe_M if your table_cards and your filter_cards are built off the same dataset, then the filter cards will update based on the available data.

in other words.

if you have two filter cards [foods] and [time_slot] and you select time_slot = afternoon, the list of dishes available will be filtered by [times_slot] = afternoon.

if pancakes appear in your [foods] card with afternoon selected, it implies that someone sold pancakes in the afternoon.

what you can do is alter your data so that you have [food_time_slot] and [actual_time_slot] on your table.

that way if some weirdo orders pancakes in the afternoon slot, the [food_time_slot] would be 'morning' because it's from the morning menue and then [actual_time_slot] would be 'afternoon' because that's when the meal was ordered.

then as you construct sum(actual) sales, it still tells the truth when you filter on [actual_time_slot] but if you had a column beast mode

sum(case when actual_time_slot = food_time_slot then actual), when you click [actual_time_slot] = afternoon then you'd only return food sold in the afternoon from the lunchtime menu.