8 Replies Latest reply on Nov 23, 2017 4:28 PM by Maximiliano Luppi

    Calculating a Mode

    ben cho


      Anyone know how to calculate a mode on a table in Tableau? I've attached some example data and the output I'm looking for is a table with the following:

       

      CityMode
      Calgary$2
      Edmonton$5
        • 2. Re: Calculating a Mode
          ben cho

          Hi Tracy

           

          I believe that only shows me how to determine my mode graphically using a bar chart. I need to calculate my mode so that I can use it as part of another calculation. I've already tried the following:

           

          WINDOW_MAX(IIF(COUNT([PRICE])==WINDOW_MAX([PRICE]),ATTR([PRICE]),0)

           

          but it only works if I have the PRICE measure included in the rows like a histogram. I need to be able to calculate with Market on the rows shelf only.

          • 3. Re: Re: Calculating a Mode
            Ramon Martinez

            Hi Ben

             

            Take a look at the attached workbook based on the data you provided.

            Sheet 1 calculate the mode for the whole series of values in measure Price. Sheet 2 calculate the mode per City

             

            This solution was originally provided by Alan Smithee in his blog post Alan Smithee Presents: Statistics

             

            I hope this helps

             

            Best

            Ramon

            • 4. Re: Re: Calculating a Mode
              ben cho

              How would I then use the mode as part of a further calculation? I tried taking the price and multiplying it by 2. When I drag the result on to rows shelf, Tableau shows all the prices again, not just the mode.

              • 5. Re: Re: Re: Calculating a Mode
                Ramon Martinez

                Hi Ben,

                 

                Take a look at the attached workbook with a solution to your question.

                 

                I created a calculated field "Price times 2" using the formula

                 

                [Price] * 2
                

                 

                I created other calculated field just for label the table column header

                 

                Then,  I added the ATTR([Price times 2]) to mark Text and format it to display the value without decimal places

                 

                See Sheet 3 of the workbook to learn how to do it.

                 

                Best

                Ramon

                • 6. Re: Re: Re: Re: Calculating a Mode
                  ben cho

                  Still need some help on this. Now I'm trying to graph the mode for each day in a time series. Ideally for each date, I'd calculate the modes for two or more sets of data and chart them on the same graph. I've attached a worksheet. Is there any way I can use a table calculation to calc the mode for each day, for each data set? I tried using the above solution with measure values but it doesn't work (I guess because I can't make them dimensions)

                   

                  DateAB
                  1-Jan-14810
                  1-Jan-1422
                  1-Jan-1484
                  1-Jan-1467
                  1-Jan-1449
                  1-Jan-1400
                  1-Jan-14510
                  1-Jan-14100
                  1-Jan-1447
                  1-Jan-1412
                  1-Jan-1415
                  1-Jan-1475
                  1-Jan-1482
                  1-Jan-1438
                  2-Jan-14210
                  2-Jan-1465
                  2-Jan-1445
                  2-Jan-1450
                  2-Jan-1414
                  2-Jan-1465
                  2-Jan-14810
                  2-Jan-14410
                  2-Jan-1424
                  2-Jan-14109
                  2-Jan-1433
                  2-Jan-1407
                  2-Jan-14710
                  2-Jan-1433
                  3-Jan-1473
                  3-Jan-1499
                  3-Jan-1484
                  3-Jan-1426
                  3-Jan-14101
                  3-Jan-14210
                  3-Jan-1431
                  3-Jan-1441
                  3-Jan-14109
                  3-Jan-1482
                  3-Jan-1455
                  3-Jan-1423
                  3-Jan-1460
                  3-Jan-1470
                  3-Jan-1466
                  3-Jan-1481
                  3-Jan-1466
                  4-Jan-1454
                  4-Jan-1474
                  4-Jan-1466
                  4-Jan-1404
                  4-Jan-141010
                  4-Jan-1498
                  4-Jan-1418
                  4-Jan-1492
                  4-Jan-1483
                  4-Jan-1438
                  4-Jan-1407
                  4-Jan-1412
                  4-Jan-1404
                  4-Jan-1448
                  4-Jan-1400
                  4-Jan-1461
                  4-Jan-14101
                  4-Jan-1405
                  4-Jan-1468
                  4-Jan-141010
                  4-Jan-1464
                  • 7. Re: Calculating a Mode
                    ben cho

                    Any help?

                    • 8. Re: Calculating a Mode
                      Maximiliano Luppi

                      Hi

                       

                      I'm not entirely sure, but I use this calculated field

                      { FIXED STR([Price]):SUM([number of records])}

                      then use it with MAX aggregation

                      I could also use it in later calculations

                      I hope it is useful

                      Regards

                       

                      Maximiliano