Best Of

If you only show the order number and not the vendor or the service codes (making sure only one row per order is being shown) you could utilize some aggregates to a filter to work and then have the user click on the order number to go down a drill path to see the order details - alternatively you'd need to do your grouping and filtering in an ETL to then only display all order records which meet your criteria)

CASE WHEN MAX(CASE WHEN `Service` = 'GB' THEN 1 ELSE 0 END) = 1 AND COUNT(DISTINCT `Service`) >= 2 AND COUNT(DISTINCT `Vendor`) >= 2 THEN 'Include' ELSE 'Exclude' END

Again though, Order ID must be on your chart but you can't have Vendor or Service included in your chart otherwise the grouping won't work correctly.

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