Seasonality Chart

dylang91

Hello, I am trying to create a Card that will help with the forecasting. We receive cases (CasesID) by Regions all year, I need to create a Card that will show all the regions and then the month as columns, the values will be all the cases submitted in that month / the total cases submitted from that region.

Where I am struggling is the date that I am not sure how to count all the cases from that month and then divide it by all the cases.

I am using a pivot on Domo to recreate this, if there is a better option please lmk maybe a heatmap?

This is my progress so far

MarkSnodgrass

There are some window functions that can probably do this for you, but pivot tables can be tricky with the window functions.

I would start by trying the mega table first. In the chart properties, under subtotal rows, you can enable the option to show percent of total. Then collapsing the items in the mega table, should just show the subtotal percent information that you want to show.

MarkSnodgrass

Also, you may want to click on Transpose under General and choose graph by month in the date range filter to get your months as columns if your data isn't already structured for that format.

dylang91

@MarkSnodgrass thanks I have never tried the Mega Table before, the percent of total only shows the total per region but not by month over the same region

ex

Region1 Jan 10 16%

Feb 20 33%

Mar 30 50%

Total Region 1 = 60

MarkSnodgrass

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`)

MarkSnodgrass

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.