Best Of

That's a bummer it doesn't show the next level down. To get this to display in a pivot table, you need to ask your CSM to enable window functions in beast modes, if it isn't already enabled. It is not enabled by default. Then you can create this beast mode with this formula to get the percent of total for each month by region:

COUNT(`caseid`) /
SUM(COUNT(`caseid`)) OVER (PARTITION BY `region`)

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.