6 Replies Latest reply on Feb 5, 2019 12:49 PM by Henry Lukas

    Getting counts within a crosstab that uses min/max measures as the dimensions

    Henry Lukas

      I'm completely new to Tableau and am trying to figure a solution to my problem.

       

      Trying to create a report using data from a ticketing system where the table in question houses the activities/movements of tickets from one status to another (think of it as a log).  As a ticket can progress through various statuses in any given month, I would like to see the status at the beginning of the month vs the end of the month, which I have already created using Min and Max variables on a Date field.

       

      Here's a sample of what the source data would look like (minus the Date field):

                                                                                                                

      TICKETSTATUS FROMSTATUS TO
      Ticket 1DraftIn Progress
      In ProgressReview
      ReviewComplete
      CompleteClosed
      Ticket 2DraftIn Progress
      In ProgressReview
      Ticket 3DraftIn Progress
      In ProgressReview
      ReviewComplete
      Ticket 4DraftClosed
      CompleteClosed
      Ticket 5CompleteClosed
      Ticket 6DraftIn Progress
      In ProgressReview
      ReviewComplete
      CompleteClosed
      Ticket 7In ProgressReview

                                                                                                                

      TICKETSTATUS FROMSTATUS TO
      Ticket 1DraftIn Progress
      In ProgressReview
      ReviewComplete
      CompleteClosed
      Ticket 2DraftIn Progress
      In ProgressReview
      Ticket 3DraftIn Progress
      In ProgressReview
      ReviewComplete
      Ticket 4DraftClosed
      CompleteClosed
      Ticket 5CompleteClosed
      Ticket 6DraftIn Progress
      In ProgressReview
      ReviewComplete
      CompleteClosed
      Ticket 7In ProgressReview

       

      Displaying my MIN and MAX variables (and using LOD on Ticket), I get the following:

                                                    

      TICKETSTATUS MINSTATUS MAX
      Ticket 1DraftClosed
      Ticket 2DraftReview
      Ticket 3DraftComplete
      Ticket 4DraftClosed
      Ticket 5CompleteClosed
      Ticket 6DraftClosed
      Ticket 7In ProgressReview

       

      What I'm having trouble with is displaying the above table in a Crosstab (such as in the pivot table below), and counting the number of tickets rather than listing them out.

      Count of TICKETColumn Labels
      Row LabelsReviewCompleteClosedGrand Total
      Draft1135
      In Progress11
      Complete11
      Grand Total2147

       

      The issue is I lose the context of the Count measure and I get a single value, with a single MIN status and a single MAX status on the entire data set.

       

      Any help or suggestions would be very much appreciated!

      Thanks,

      Henry