2 Replies Latest reply on Apr 15, 2013 1:52 PM by Jonathan Drummey

    How do I count the number of days for each sum of the number of records?  E.g. during the time period, 3 days had 4 cases, 6 days had 5 cases, etc.

    Renelle Risley

      I have a Netezza database containing patient data.  I cannot post the workbook due to patient info issues.  Each data row contains patient info including the data/time of a procedure.  I want to know how many days contained the various total number of cases.  For example, there are 31 days in which we had one case, 65 days had a total of 3 cases, etc.  Thanks for your help.  P.S. I'm not a programmer & don't know anything about custom SQL.  I'm hoping to do this all in Tableau with calculated fields.  Thanks.

        • 1. Re: How do I count the number of days for each sum of the number of records?  E.g. during the time period, 3 days had 4 cases, 6 days had 5 cases, etc.
          Noah Salvaterra

          Good question. The problem here is that, while an aggregate measure can be created to count the number of visitors on a given day, we then want to turn around and use this as a dimension. I've been fiddling around with indexes and such, but can't quite get it to compute without leaving tableau. I am at a point where I'd probably reach for custom sql.

           

          I made up some dummy data as you described with date and patient ID (you may need to truncate your date/time to date using datetrunc). I did manage to come up with a frequency histogram by stacking individual marks which feels a bit like progress (but isn't what you asked for). Hopefully someone else can continue where I left off or a quick histogram will do the job.

           

          FYI in the dummy dataset there are:

          105 days with 1 case

          119 with 2

          87 with 3

          66 with 4

          28 with 5

          11 with 6

          1 with 7

          2 with 8.

           

          http://public.tableausoftware.com/static/images/Hi/Histogramoncalculatedfield/HistogramTrick/1.png

          • 2. Re: How do I count the number of days for each sum of the number of records?  E.g. during the time period, 3 days had 4 cases, 6 days had 5 cases, etc.
            Jonathan Drummey

            See the attached for an all-Tableau no-Custom-SQL solution. I used Superstore Sales, with Order Date for Procedure Date, and Number of Records as the Number of Records. Here's how I put it together, first in the crosstab, then in the histogram view:

             

            1. Add Order Date to the Rows Shelf, set it to Exact Date, make it discrete.

            2. Add SUM(Number of Records) to the Rows Shelf view.

            3. Click on the pill and set it to discrete, the pill turns blue.

            4. Click on the pill again and turn off "Ignore in Table Calculations". This is the special sauce that will let Tableau partition table calculations on the results of a measure.

            5. Create a table calculation (I called it # of Days) that has the following formula: IF FIRST()==0 THEN WINDOW_COUNT(COUNTD([Order Date]),0,IIF(FIRST()==0,LAST(),0)) END. In version 8 the formula can get rid of the ,0,IIF(FIRST()==0,LAST(),0) optimization.

            6. Put # of Days on the Text Shelf, set the Compute Using to Order Date (it will automatically partition on the SUM(Number of Records) because of step 4). This gives us a crosstab that we can validate.

            7. Duplicate the worksheet to start building the histogram.

            8. Drag both Order Date and SUM(Number of Records) onto the Level of Detail shelf.

            9. Drag Number of Records from the Measure Shelf to the Columns Shelf to get a green (continuous) SUM(Number of Records).

            10. Drag # of Days from the Text Shelf to the Rows Shelf. Tableau chooses Shape for the Mark Type to draw a scatterplot.

            11. Set the Mark type to Bar.

            12. Resize the bar to make it as wide as possible using the Size slider.

            13. Ctrl+Drag a copy of the # of Days pill from the Rows Shelf onto the Filter Shelf, Filter for Special->Non-Null values. This causes Tableau to get rid of all the extra values created by Order Date being on the level of detail.

            14. Clean up the view - turn on mark labels, edit the tooltip to get rid of Order Date, etc.

             

            One extra note: I'm using a continuous (green) SUM(Number of Records) because if we just use the discrete SUM(Number of Records), the histogram wouldn't be accurately spaced. You can see that in the "wrong histogram - no #19" view, there's nothing for 19.

             

            Jonathan