7 Replies Latest reply on Jan 3, 2013 6:22 AM by Jonathan Drummey

    Custom cross tab heat map?

    Glen Park

      Hello everyone,

       

      I'm attempting to create a worksheet with a cross tab where the numbers in the cells change color based on a specific threshold, like a heat map.  I have seen a couple short examples of how to create different colored txt across a cross tab but I'm running into problems trying to get this specific solution rolling.

       

      Basically it's a short cross tab that's based on a count of orders and their status over time...an order can be in a status of either "ordered", "canceled", or "shipped".  The time component is defined by the order date.  I'm looking to do a couple things with this...#1.  I want to order totals in "ordered" status past 5 days old flagged as red.  Ideally all orders should be either in "shipped" or "canceled" status before 5 days is up.  Secondly, when the cancels total is at 200 or above on a given day, that total is also flagged as red.  I have attached a rough mock-up in a screenshot to illustrate what I'm trying to do. 

       

      Any help is appreciated.

       

      Thanks!

      Glen

        • 1. Re: Custom cross tab heat map?
          Alex Kerin

          In instances like this it's really helpful to know what your data looks like - can you post a tbwx with mocked up data (if sensitive)?

          • 2. Re: Custom cross tab heat map?
            Glen Park

            What's the best and easiest way to mock up data?  Right now I'm connecting to a published Tableau extract which is based on a large star schema and the order data is rather sensitive.  Should I just create some simplified version of it in an Excel spreadsheet and use that as the data source?  Since I'm really only interested in order date, order status, and the count of orders (based on order id), maybe that's all I need to include?

             

            *edit*

            I created a really simple data mock up with a handful of orders and their dates and statuses.  Given the small number of rows, we could go with flagging red any orders at 5 days or order...and also when cancels amount to quantities of 2 or more.

            • 3. Re: Custom cross tab heat map?
              Glen Park

              Extra note, I found some examples of "highlight" tables which could conceivably work...but they only work with only 1 measure.  In my case, I'm looking to highlight for two different scenarios (counts and time).

              • 4. Re: Custom cross tab heat map?
                Jonathan Drummey

                Measure Names/Measure values gives you only one Marks Card, so there's only one Color Shelf. You can do what  you want with a multiple axis crosstab, see http://public.tableausoftware.com/views/conditionalformattingv4/Introduction for details.

                 

                Jonathan

                • 5. Re: Custom cross tab heat map?
                  Glen Park

                  Thanks for the info Johnathan, I'm definitely digging this stuff!

                  • 6. Re: Custom cross tab heat map?
                    Glen Park

                    Ok so I've created sort of a derivative of the "Color Dynamic - Chosen Cells" workbook.  The difference is that I have placed my order types by column with order dates by row.  I have also gone into the "Analysis" menu and enabled grand totals by row.  The problem I have is that when I select an order type from the parameter, the numbers for the correct order type are highlighted but a second set of numbers gets highlighted and overlays the grand total column.  Any idea how to make that not appear?  I have attached an updated sample .twbx file for reference.

                     

                    Thanks,

                    Glen

                    • 7. Re: Custom cross tab heat map?
                      Jonathan Drummey

                      Hi Glen,

                       

                      Grand Totals are a separate computation at a coarser level of granularity. In the case of the Grand Total on Columns, the GT column is a SUM(Number of Records) for each Order Date, effectively removing Order Status from the Columns Shelf. However, you have the Color Status field as a dimension on the Color Shelf, which is based on the Order Status. So that's bringing effectively bringing Order Status back into the Grand Total calculation and causing it to partition on the combination of Order Date and Color Status. Since there's only one cell for each GT, those multiple rows end up with overlapping text in the Grand Total column.

                       

                      The solution here is to use an aggregate for the Color Status calc, however because of sparse data you need to be careful of where you do the aggregation. Aggregating the Color Status won't work:

                       

                      In the MIN Color worksheet, I set the Color Status to aggregate on MIN(Color). However, this only works in this (sparse) because the the highlight color (Orange) is alphabetically sorted after the Black non-highlight color. If you use another background color - I created a variant calc that highlights with Orange, otherwise Z - then when an orderstatus that has sparse data is chosen, like "canceled" or "ordered", the Grand Total column will get two colors, you can see this in the MIN Color Z worksheet for the "ordered" status. This is because there's no data for the ordered status for 12/23, so the only MIN(Color Status) being returned is Z. I set this up in the MIN Color Z explained worksheet.

                       

                      In the Color ATTR(orderstatus), instead of aggregating the Color Status (which as we can see can return multiple values to the Grand Total column), we do the aggregation on the Order Status. I used the ATTR() aggregation here because this works for all the regular rows, and within the Grand Total column will (almost) always return Null, which the Color calc will assign to black. The one case where it would not is if there was only one distinct order status for a given order date, you can see that in the Color ATTR(orderstatus) breaks worksheet. In that case you'd need to do something different like have a separate worksheet for the grand total or use Custom SQL to duplicate the data and build your own grand total as a separate dimension.

                       

                      Jonathan