Best Of

To your thought about heatmaps, I do like to use those and there are two different heatmap options: Heat Map under Other Charts and Heatmap Table under Tables and Textboxes. With the Heat Map under Other Charts, you can put the Month in Category 1 and the region in Category 2 and the count of case id's in the values section. The "heat" will show you where the highest areas are.

The Heatmap table would require you to create a beast mode for each month of the year and be dragged over as columns. You could select the independent column ranges under the scale properties to have each month be "heated" individually if you want. Your beast mode to get the percent for each month for each region would look like this:

SUM(CASE WHEN MONTH(`date`) = 1 THEN 1 ELSE 0 END) / COUNT(`caseid`)

This would be the beast mode for January. You would need to do one for each month and change the month = value each time.

Hope this helps.

Hey,

If I were to be going through this I would first look in the ETL and run a preview to see the output of each ETL tile (depending on the amount of data you have because it only shows a preview). You could spot duplicates or something looking off and know which tile needs to be edited.

I would also go to your data output of this ETL in the data center and look at the data, filter it down to a specific case where you saw an issue in the pivot. For example, the 0x0 locker that is showing 10 instead of the desired 5. This may help you spot the issue.

If you still cannot pinpoint the error, I would also look in the pivot table you have created and make sure in each value aggregation type you are taking a sum where you need or maybe you want to be taking the count instead. It is possible you may want to be filtering by a distinct count of an attribute as well, this is where a beast mode would come into play.

Another note: You did mention a recursive aspect to this ETL and typically, you want some logic in the ETL to make sure you are not counting duplicates. Hopefully this gives you some more ideas to help you troubleshoot and pinpoint the issue.

Best,

Nick

@hqu I believe I have come up with a solution for you. It involves creating a MySQL dataflow and creating a function to calculate the next workday and then using the function in your call to your dataset. Here's what to do

Create a MySQL dataflow and add in your dataset and the Domo Calendar dataset.

Click Add a Transform and choose SQL and add the following which will allow you to run it multiple times when building out your dataflow

DROP FUNCTION IF EXISTS NextWorkday;

Add another SQL transform and add the following to create the actual function:

CREATE FUNCTION NextWorkday (startdate DATE, days INTEGER)
RETURNS DATE DETERMINISTIC
BEGIN

DECLARE nextdate DATE;

SELECT `dt` INTO nextdate
FROM `domo_calendar`
WHERE `dt` >= DATE_ADD(startdate, INTERVAL days DAY) AND
`isWeekday` = 1 AND `isHoliday` = 0
ORDER BY `dt` ASC
LIMIT 1;

RETURN nextdate;
END

Now add a table transform where you can call this function as another column along with the rest of the columns that you need for your dataset like this:

SELECT NextWorkday(`dt`,`days`) as NextWorkday, `dt`
FROM `mytable`

You'll need to change this to match your fields. Hope this helps.

@hqu It doesn't. It's a simplistic model. As Mark said you'd need to pull in the Domo Dimensions - Calendar Dates dataset into an ETL. It's a bit more tricky with Magic as you can't do a conditional join with between or less than / greater than. You can do this with a MySQL dataflow but it will run slower than a Magic ETL. Magic will run slowly as well as you have to do a cartesian join to get all possible combinations and then do your filtering with a filter tile to get the information you want, then for each record group on the unique key, calculate the sum of weekday field and the sum of the is holiday field. Then subtract is holiday from weekday to get the number of business days in-between.

Here's an example I did with a MySQL dataflow (dim_calendar_dates is the Domo Dimensions - Calendar Dates dataset):

select p.`id`, count(`id`) as calendar_days, sum(`isWeekday`) - SUM(`isHoliday`) as business_days
from `my_table` as p
join `dim_calendar_dates` as d on d.`dt` >= p.`ship_date` and d.`dt` <= `delivered_date`
group by p.`id`

This is also assuming you're wanting American holidays, if you need holidays from a different country you'd need to get that dataset somewhere else instead of the Domo Dimensions connector.

There is this beast mode function that is available, but it requires two dates, so may not suit your needs, but thought it is at least worth pointing out.

DATE_WORKING_DIFF(expr1,expr2)

Returns the number of days between the dates expr1 and expr2, excluding weekends (Saturdays and Sundays). The result is negative if expr1 is after expr2.

Hi @hqu

I've posted previously an article about adding business days to a date with a simplistic algorithm. You can read about it here: https://dojo.domo.com/discussion/52678/domo-ideas-conference-beast-modes-add-business-days#latest

Here's a link to the video of my session outlining this beast mode: https://www.youtube.com/watch?v=gO8OLpsAk4M&index=6