1 Reply Latest reply on Sep 4, 2018 2:48 PM by Patrick A Van Der Hyde

    Daily Percentage Incorrect

    TaRan Wilson

      This dashboard has been giving me fits for days and I just can't seem to figure out how to get it work properly.

       

      At my job, we have a number of devices that we monitor and they can be in various states of up or down. I'm trying to track what the status of a certain device type is at the end of the day for the last 30 days. We have a timestamp that keeps track of a device's status at the end of the day, so I'm trying to capture that for all devices and give it as a percentage.

       

      For example, at the end of the day on August 7th, the number of CCTVs that are set to the status of Operating should read 96.89% (we have a separate piece of proprietary software that keeps track of these numbers for billing purposes, but I need them in a Tableau dashboard for reports/meetings). It is reading 96.66% according to the dashboard (which is inaccurate). For August 6th it should read 96.06%, but it is reading 95.71%. And so on and so forth.

       

      One of the biggest concerns is that on August 2nd and before, the RDS devices are reading 86.36%, but they should have been at 100%. I cannot figure out why it's reading like this.

       

      I'm hoping someone can take a look and see something I am not. There's a worksheet named test1 which I have stripped down to the bare minimum of what should be needed to create the graph. test2 is a variation of the same thing. test3 may not work because the data source was too large for me to extract for use (which is why I created the "trim" data source version). NewTest is a worksheet I created with a few different parameters than before (mostly trying not to join to any other tables), in an effort to get the correct results. I figure if I can find out why the RDSs aren't reporting properly, that will trickle down and fix the issues with the other devices being a few ticks off.

       

      Below is an SQL query that the proprietary software uses to come up with the device percentages. It's what I've been basing my dashboard on; I've been trying to mimic it, to an extent, in hopes I'd get my percentages correct. Perhaps it'll help.

       

      SELECT CAST (CCTV1.location AS REAL) / CAST (CCTV2.location AS REAL) * 100

      FROM (SELECT COUNT (*)location FROM locations

              JOIN locoper ON locoper.location = locations.location

                      WHERE locations.status IN ('OPERATING', 'Waiting - Equipment', 'Waiting - Fiber Cut', 'Waiting - GDOT', 'Waiting - Loops', 'Waiting - Other', 'Waiting - Power', 'Waiting - Restrict')

                      AND locoper.failurecode IN ('CCTV', 'CCTV-IR') AND locations.type = 'OPERATING' AND locations.billingschema = 'Standard') CCTV1,

      (SELECT COUNT (*)location FROM locations

              JOIN locoper ON locoper.location = locations.location

                      WHERE locations.status IN ('DOWN', 'OPERATING', 'Waiting - Equipment', 'Waiting - Fiber Cut', 'Waiting - GDOT', 'Waiting - Loops', 'Waiting - Other', 'Waiting - Power', 'Waiting - Restrict')

                      AND locoper.failurecode IN ('CCTV', 'CCTV-IR') AND locations.type = 'OPERATING' AND locations.billingschema = 'Standard') CCTV2;

       

      Below is what the CCTV and CMS devices should be from July 26th to August 7th. The RDS devices should read 100% that whole time. I can provide the percentages for the other devices if needed.

       

      CCTV

      August 7 - 96.89%

      August 6 - 96.06%

      August 5 - 95.57%

      August 4 - 95.80%

      August 3 - 95.92%

      August 2 - 96.03%

      August 1 - 96.16%

      July 31 - 95.89%

      July 30 - 95.88%

      July 29 - 95.26%

      July 28 - 95.26%

      July 27 - 95.26%

      July 26 - 93.81%

       

      CMS

      August 7 - 97.65%

      August 6 - 97.06%

      August 5 - 97.02%

      August 4 - 97.60%

      August 3 - 97.60%

      August 2 - 98.80%

      August 1 - 98.80%

      July 31 - 98.21%

      July 30 - 99.40%

      July 29 - 98.81%

      July 28 - 98.81%

      July 27 - 98.81%

      July 26 - 97.60%

       

      Thanks for any help than can be given. I've been working on this for days and I've exhausted all my ideas to make this work.

        • 1. Re: Daily Percentage Incorrect
          Patrick A Van Der Hyde

          TaRan,

           

          Let's address the raw numbers instead of the percent of totals to see what is going on there.  I checked and the RDS values for the viz New Test show 330 to 328 through 8/6 to 8/13.  Based on what you mention above, the expected value would be 330 or 328 for all values.  I suggest focusing in on why there are differences here in the numbers by looking at the raw values of locations that are in each.  My guess is that one of the filters is causing this. 

           

          Patrick