2 Replies Latest reply on Mar 29, 2015 11:03 AM by rafael diaz

    Need urgent help to fix logic in error in a threhold calculation

    rafael diaz

      Hello,

      I need a little help figuring out what I'm doing wrong in my threshold calculation as I think I've been looking at this so long that I'm missing the obvious.

       

      Tableau Version: 8.2

       

      Scenario: We're creating a rather simple operational status dashboard for a customer who wants to know the status of their assets in the field.  There are two levels of statuses. There's an overall status chart that provides the executives a quick overall status of all of their assets which consists of aggregating all of the assets broken out in asset type categories, and compared against threshold per asset type. The status can be Operational, Partially Operational, and Inoperative. A second dashboard is a more detail version of the above where each asset is broken out with their individual status shown. The user can select any date they would like to see the status of their assets. For our customer, they report on a weekly basis to their bosses. I will use the Police Patrol vehicle status in this example to describe the issue. So if they want to see the status of their system on day 20, then all of the individual asset statuses will be counted up based on their status on that particular day.

       

      Db Organization: So the user would update their statuses by entering today's date, the asset type and name, and then would enter the asset's status as of today. The Db will aggregate these entries into Start and End dates. Only changes in statuses are stored. So for example, if the user on day 1 set the status of asset 1 to operational and then on day 12 that asset went down, the user would go in on day 12 and enter the status of Inoperative.  On day 20, the asset was repaired so the user would go in and set the status back to Operative. In the Db you will see two rows of data. On the 1st row, the Start date column will have day 1 date and the current status (this is the very first time they have entered data in the system). The End date will equal to the Start date. 12 days later the asset went down. The Db will set the End Date to day 12. The next row will have a Start date of day 12 with a new operational status = Inoperative. On day 20, the user sets the status back to Operative so the End date of row 2 is now set to Day 20. The next Db row (row #3 now) will set the Start Date as day 20 with a new status of Operational.  Hopefully it's clear that there will never be any overlaps of asset status periods. BTW, this is all mock data and made up asset names.

       

      Algorithms Used: To calculate the overall status I have a couple of calculated fields.

       

      Assets in Service Threshold - This is a case statement that compares each asset type by counting up all instances where that asset status = Operational and that the date of those entries are between the parameter date selected and the Start_Date of when that asset first entered that status against that asset type threshold set by the customer using the threshold parameter field.

      Operational Assets - This counts up each individual asset by asset name only if that asset's status = operational and it falls within the date range calculated by DateIsBetween.

      DateIsBetween - This determines if the Current as of parameter date selected by the user is between the Start date and today's reporting date or the current date this dashboard is being looked at (End date defaults to Today() ).

      Current as of - This is the Date parameter control that the user can set to any time in the past that they want to view the status of.

      Patrol vehicle Threshold - This value represent the minimum number of vehicles that have to be operational before panic sets in. If the value is below this threshold, the overall status should be set to Degraded/Inoperational.

       

      Problem: The problem is that if you look at the Police Overall Status sheet, the police Patrol vehicle line shows 11 vehicles were operational (Operational Assets) on 3/27/2015. The  Patrol vehicle Threshold parameter is set to 10. The color of the dot should be green since the Assets in Service Threshold calculation does the following:

             IF [Operational Assets] < [Patrol vehicle threshold] THEN "Red Alert!" ELSE "OK" END

      Since Operational Assets = 11, then the ELSE should be executed, which should be Green in color

       

      Can some one check this over and let me know what obvious thing I'm overlooking? In the meanwhile I'll have to look for a backup plan since our demo is this coming Monday.

       

      Thanks in advance!

      Rafael

        • 1. Re: Need urgent help to fix logic in error in a threhold calculation
          Jeff D

          Hi Rafael, I hope one of the Tableau wizards is able to help.  In the meantime, here's my attempt to be of assistance.  I noticed that the value for "Assets in Service threshold" Calculation is always "Degraded/Inoperative" (you can see this on the sheet and also if you drill down into the underlying data), which would explain why all assets show up as Red.

           

          Attached is an experiment I did. I added a new calculation ("Calculation1") which uses slightly different logic than your calculation.  I removed the Index filter (that's at least one step above my level) and used this new Calculation in place yours.  This seems to have the desired behavior on this sheet.  I'm sure I must be missing something here, but perhaps this will give you some ideas.

          • 2. Re: Need urgent help to fix logic in error in a threhold calculation
            rafael diaz

            Hey Jeff,

             

            You actually hit it right on the head! The calculations you created was really what I wanted to do but when I tried something similar to what you did, I got the error "Can't mix aggregate and non-aggregate operations in a CASE statement". I didn't think to try converting the calculation into a measure like you have it.

             

            I wasn't sure if the multiple level IF statement would execute properly because I know with software compilers, they try to optimize performance by exiting the IF-THEN statement as soon as one of the conditions evaluate to TRUE but apparently Tableau evaluates each OR statement separately. However I verified this works by changing the data so that multiple assets changed at the same time and it worked like a charm.

             

            I've only started using Tableau like about 6 months ago so I'm still learning this stuff.

             

            Thanks for your help!