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!
Asset Thresholds.twbx 186.1 KB