9 Replies Latest reply on Aug 30, 2018 6:34 AM by Jen Shepherd

    How do I make my calculated field display in tooltip using the mean +/- StdDev?

    Benny Feinsilber

      Hi all!

       

      I'm running into some difficulties using a custom calculated field to display different arrows depending on the value in tooltip.

       

      I'm consistently updating an Excel document that I'm pulling from, and my goal is to have any number that falls above the mean + standard deviation of the whole column to display "▲"

       

      Any number that falls below the mean - standard deviation of the whole column should display "▼"

       

      Here is the formula I've been using. It says it's a valid expression, however when I drag it into the tooltip it doesn't display any arrows:

       

      IF SUM([Room Nights]) > AVG([Room Nights]) + STDEV([Room Nights]) THEN "▲" ELSEIF

      SUM([Room Nights]) < AVG([Room Nights]) - STDEV([Room Nights]) THEN "▼"

      END

       

      Does anybody know what I can do to make it show up? Would it be an SQL command I need to use? Otherwise I have to manually put it the Avg +/- StdDev which takes much more time and would be easier if it was automatically changed for each data refresh.

       

      Thanks!

        • 1. Re: How do I make my calculated field display in tooltip using the mean +/- StdDev?
          swaroop.gantela

          Benny,

           

          I think the issue may lie in comparing a row value to the AVG and STDEV across many rows.

           

          Not having seen the data or your setup, my impression would be that SUM(Room Nights)

          is the calculation for a particular customer, but the AVG and STDEV need to be across a group of customers.

           

          And so I think the calculation may need to look like:

          IF SUM([Room Nights])>([Room Nights AVG]+[Room Nights STDEV]) THEN "▲"

          ELSEIF SUM([Room Nights])<([Room Nights AVG]-[Room Nights STDEV]) THEN "▼"

          END

           

          Where

          Room Nights AVG is:

          WINDOW_AVG(SUM([Room Nights]))

           

          and Room Night STDEV is:

          WINDOW_STDEV(SUM([Room Nights]))

           

          These will need to have a there table settings set to a

          "Compute Using" of Customer.

          Please see graphic below and workbook v10.3 attached in the Forum Thread.

           

          280188std.png

          • 2. Re: How do I make my calculated field display in tooltip using the mean +/- StdDev?
            Benny Feinsilber

            Hi Swaroop,

             

            Thanks for the help!

             

            I tried doing exactly what you said, however it still wasn't appearing under the tooltip.

             

            The workbook you attached shows the arrows being displayed as a label. However, I'm trying to have the arrows displayed under the tooltip. Any time I put the "FlagOutliers" calculated field under the tooltip, the arrows show up sometimes but very randomly and definitely not for the values it's supposed to show/not show for. It should only show arrows if it's a number that falls outside of the mean +/- the standard deviation, and I'm seeing arrows display within that range. Do you know how I would be able to do that?

             

            Thanks again.

            • 3. Re: How do I make my calculated field display in tooltip using the mean +/- StdDev?
              swaroop.gantela

              Benny,

               

              My apologies, I got distracted by the window part and neglected the tooltip.

              I put the same calculation above in my fake dataset, and it appeared to work correctly in the tooltip.

              (please see workbook attached in the Forum Thread)

               

              Because [FlagOutliers] is a table calculation, its results are heavily dependent upon the

              table calculation settings for Specific Dimensions and for when to restart.

              These in turn are heavily dependent upon the pills that you have on the worksheet

              and where they are located.

               

              Would it be allowable for you to post a screenshot of your worksheet, including the pills on their shelves?

              If the pill names are sensitive, you can black them out.

              It would also be helpful to see a screenshot of your table calculation settings for Flag Outlier.

              It's possible that there may be nested calculations, and would like to screenshot of that too.

              • 4. Re: How do I make my calculated field display in tooltip using the mean +/- StdDev?
                Jen Shepherd

                Hi Benny,

                 

                Try this LOD calc instead:

                 

                IF SUM([Room Nights]) > sum({exclude [Customer]: avg([Room Nights]) + STDEV([Room Nights])}) THEN "▲"

                ELSEIF SUM([Room Nights]) < sum({exclude [Customer]: avg([Room Nights]) - STDEV([Room Nights])})THEN "▼"

                END

                 

                I just tried this calc in the workbook Swaroop provided and it is showing ▲ if the sum of Room Nights is > 14 and ▼ if less than 2 (which I think is correct given an average of 8.16 and STDEV of 6.146), but nothing if within those values.

                 

                 

                Attached workbook is in 2018.1.

                 

                Best,

                Jen

                • 5. Re: How do I make my calculated field display in tooltip using the mean +/- StdDev?
                  Benny Feinsilber

                  Thanks Swaroop! Here's a screenshot. I have blocked out some sensitive information, however the circle is where I want the tooltip to display an arrow if that room night number is greater/less than the mean +/- the standard deviation.

                   

                  However, when I do the calculation that you showed, it doesn't show up.

                   

                   

                  I have also included a screenshot of the FlagOutliers table calculation as well as two custom calculations I made to format the calendar view:

                   

                   

                   

                   

                   

                  Let me know if these help. Thanks!

                  • 6. Re: How do I make my calculated field display in tooltip using the mean +/- StdDev?
                    Benny Feinsilber

                    Hi Jen,

                     

                    Thanks for the help!

                     

                    I tried doing that in my workbook, and some of the arrows would show up at seemingly random dates. I've attached screenshots below:

                     

                     

                    As you can see, there is a positive arrow on Oct 18. There is a negative arrow on Oct 19, however the total room nights for that day is higher so there's no way that should be happening.

                    • 7. Re: How do I make my calculated field display in tooltip using the mean +/- StdDev?
                      swaroop.gantela

                      Benny,

                       

                      Thanks for the screenshot. That does help.

                       

                      You have quite a lot of pills on columns, rows, and details,

                      and so the straightforward table calculation setting shown will not suffice.

                      Also there are nested calculations which may require different settings

                      The nested calculation of Room Nights STD will also need its table calculations set.

                      (please see graphic below, you'll need to pull down the circled menu

                      to see the other calculations requiring settings).

                       

                      All that aside, and given the number of pills on your screen,

                      Jen Shepherd 's solution of a LOD calculation is the better one.

                      While that works with the test set, it will likely require some adjustments

                      to suit your real set, possibly adding the filter to context or adjusting the

                      dimensions in the definition.

                       

                      My test set is not well replicating your true setup.

                      I'll see if I can mock up a dashboard similar to yours.

                      But if it not, may need a small of set of fake data in the

                      same format as your true set. Though I understand if that

                      may be too much of an undertaking.

                       

                      280188stdNest.png

                      • 8. Re: How do I make my calculated field display in tooltip using the mean +/- StdDev?
                        swaroop.gantela

                        Benny,

                         

                        I took another go at it, using Jen's LOD method.

                         

                        I made a calendar similar to yours.

                        I don't think I have a parallel calculation to your RoomNights

                        (would you mind posting a screenshot of its equation?).

                        I colored each calendar square using a count

                        of the number of times a stay date occurred on that day:

                        { FIXED [Stay Date]:COUNT([Stay Date])}

                         

                        The only true dimension on the sheet was the stay day, so I calculated the

                        average as an LOD without any dimensions:

                        { AVG ( [StaysPerDayLOD] ) }

                         

                        similarly for stddev:

                        { STDEV ( [StaysPerDayLOD] ) }

                         

                        So the upper limit became just:

                        [StaysThisDay AVG]+[StaysThisDay STD]

                         

                        So the FlagOutlier became

                        IF [StaysPerDayLOD] > [LimitUpper] THEN "▲"

                        ELSEIF [StaysPerDayLOD] < [LimitLower] THEN "▼"

                        END

                         

                        It didn't seem to be affected by the filter settings.

                         

                        In the dashboard of the workbook v10.3 attached in the Forum Thread,

                        I showed the dispersion in the left panel and the calendar on the right.

                        with an action so that clicking on a date will highlight it in the other.

                         

                        Please adjust the workbook as needed to more closely match yours.

                        • 9. Re: How do I make my calculated field display in tooltip using the mean +/- StdDev?
                          Jen Shepherd

                          Hi Benny,

                           

                          I'm glad the LOD approach is helpful, but like swaroop.gantela, I didn't realize that your viz is a calendar layout (pretty cool, btw!) based only on dates.  For future reference, posting at least a screen shot of what you're trying to build and any calcs already in play (preferably via a packaged workbook) in your initial post will help you and responders get to the correct answer more quickly. It sounds like you and Swaroop have this well in hand, but I'll check in again in case I can assist further.  Teamwork!

                           

                          Jen