Best Of

Hi @user077529

How is your raw data formatted? Do you have a single column for the service type and multiple records for each order or are the different services separate column values and you have one row for each order?

Hi @micheleb

You'll need to put everything in the same format and then convert it to a date. You can convert the date strings using a REGEX_REPLACE function in a formula tile:

REGEXP_REPLACE(`Date`, '^(\d{2})\/(\d{2})\/(\d{2})$', '$1/$2/20$3')

This will check for a 2 digits / 2 digits / 2 digits format and then put a 20 in the last field to default to this millennium so that you have a 4 digit year. If it doesn't match the format it will still just return the string so your MM/DD/YYYY format strings will be untouched.

Once they're in the same format you can then use an Alter Columns tile to change the type of the date column from string to Date.

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.