6 Replies Latest reply on Apr 4, 2017 12:42 PM by Debasish PaiMazumder

    calculate number days exceeding certain thresholds

    Debasish PaiMazumder

      Hello All,

       

      I am a newbie in Tableau. I have developed a daily map of temperature (Please see the attached) for three months. Now I would like to calculate number of days (out of specific period) temperature greater certain thresholds. My filters will be dates and thresholds.

      For example - out of three months (92 days), I could choose specific period (25 day period) in the filter to calculate number days out of 25 days temperature greater certain thresholds.

      Another filter will be single value slider for different thresholds.

       

      Any advice?

       

      with regards

      -Deb

        • 1. Re: calculate number days exceeding certain thresholds
          Brian Dudley

          Consider using a parameter for the threshold value. Then you can add a calculated column that determines, for each record, if it exceeds the threshold -- returning a 1 or 0 is probably best. Then this field can be summed based on the date filter and any level of detail selections.

           

          Trying to handle the temperature threshold with a filter, I believe, would be considerably more difficult.

          • 2. Re: calculate number days exceeding certain thresholds
            Debasish PaiMazumder

            Hi Brian,

            Thanks for your response. I am able to follow first two steps. I need little help in step 3 - "Then this field can be summed based on the date filter and any level of detail selections." How do I resolve this step?

            with regards

            -Deb

            • 3. Re: calculate number days exceeding certain thresholds
              Debasish PaiMazumder

              Hi Brian,

              Thanks for your response. I am able to follow first two steps (attached). I need little help in step 3 - "Then this field can be summed based on the date filter and any level of detail selections." How do I resolve this step?

              with regards

              -Deb

              • 4. Re: calculate number days exceeding certain thresholds
                Brian Dudley

                You can use the parameter value in field calculations, just like other fields. My first thought, about making a field with value 1 or 0 is probably not optimal.

                 

                What you want is a distinct count of days for the granularity. Something like:

                 

                CountD( If [Temperature] > [Temperature Threshold] Then [Date] End)

                 

                The visualization in your workbook is doing something odd. It is somehow restricted to a single day, no matter how many are selected. I built a new viz and got the expected behavior, except I had to deal with temperature summing, so it needed a different aggregation (Avg or Median). Putting

                 

                It is also possible to define a calculated field like [Temperature] > [Temperature Threshold] and then use this boolean calculation in other calculations.

                 

                I'll need to play with this a little more, because there are some things going on that I'm not expecting. Perhaps others will comment as well.

                 

                You might want to change your Date into a real date value rater than working with it as a string as that will give you a few more options for filters and roll-ups.

                • 5. Re: calculate number days exceeding certain thresholds
                  Brian Dudley

                  A few more thoughts...

                   

                  I filtered down to a city name that existed in multiple states/counties. This made it easier to check my work.

                   

                  I also worked with a text visualization when coming up with the calculations. You can apply the same filters to multiple worksheets to see the same results in different forms.

                   

                  Use the Number Of Records measure and think about adding a CountD([Date]) (NumberOfDays) measure to contrast with the DaysAboveThreshold measure.

                   

                  All of these can be applied to the text attribute and will show on the map.

                  • 6. Re: calculate number days exceeding certain thresholds
                    Debasish PaiMazumder

                    Hi

                    Thanks Again. I have tried to use CountD( If [Temperature] > [Temperature Threshold] Then [Date] End) but still getting 0 and 1. Please see the attached. I believe the main problem is that I am not able to make the "Date" filter as a range. My "Date" filter is giving me single day. How do I get the range?

                     

                    I have tried to change my Date into a real date value but it slows down my display substantially.

                     

                    with regards

                    -DebScreen Shot 2017-04-04 at 1.40.27 PM.png