1 Reply Latest reply on Jul 10, 2012 9:05 PM by Jonathan Drummey

    How do I calculate the number of occurences?

    Chris Shadbolt

      I have a file of ISBN identifiers in sheet 9. How do I calculate the number of cases where I have a single copy, duplicate copies...up to the maximum number of copies

        • 1. Re: How do I calculate the number of occurences?
          Jonathan Drummey

          Hi Chris,

           

          Attached is one method, using the following calculated fields:

           

          Count ISBN with the formula COUNT([ISBN/Volume])

          WS # of Records with the formula

          IF FIRST()==0 THEN

              WINDOW_SUM(SUM([Number of Records]),0,IIF(FIRST()==0,LAST(),0))

          END

          This table calculation will have the Compute Using set to ISBN/Volume.

           

          And Marks Filter with the formula

          FIRST()==0

          This table calculation will also have the Compute Using set to ISBN/Volume

           

          Then you do the following to create the text table view shown in the Window Sum worksheet:

           

          - Put Count ISBN on the Rows shelf, right-click on the pill to make it Discrete, right-click on the pill again to uncheck "Ignore in Table Calculations"

          - Put ISBN/Volume on the Level of Detail shelf

          - Put WS # of Records on the Text shelf, right-click on the pill to set the Compute Using to ISBN/Volume.

          - Put Marks Filter on the Level of Detail Shelf, right-click on the pill to set the Compute Using to ISBN/Volume. Then Ctrl+drag a copy of the Marks Filter pill from the Level of Detail Shelf to the Filter Shelf, and filter only for True. (If you are using Tableau 7.0.2 or higher, then you should be able to go directly to the Filter Shelf and skip the Level of Detail step).

           

          I also set up a histogram-like view. Due to the number of ISBNs with only one instance, I set this up with a logarithmic scale.

           

          Cheers,

           

          Jonathan