2 Replies Latest reply on Feb 14, 2018 4:29 PM by Scott Henry

    Data structure for running totals with dates

    Scott Henry

      Hello - my goal is to show a visualization of user password resets. The problem I'm running into is that I've never visualized with the structure of data that I'm working with. I don't have snapshots of each day, I just have one row per user that shows their status (completed, exempt, or pending). As soon as they update their password, their record goes from 'pending' to 'completed', and I no longer have their prior record.

       

      I want to show a total of all users, with a percentage of completed vs pending, and watch that percentage creep up as time goes on. Because I don't have a count day by day of how many records are in which status, I'm struggling to understand how to visualize this, and I'm feeling like I need to go back to the drawing board with the data structure. I feel like their must be a way to basically take a fixed sum of people (distinct Id values), then subtract a running total of 'completed' from it.

       

      My end goal is to be able to tell hour by hour how many of my people are 'completed' versus 'pending'. It seems like I need to subtract the running sum of 'pending' from the running some of completed, but I can't seem to figure it out.

       

      Any ideas? Sample attached with a some futile attempts.

        • 1. Re: Data structure for running totals with dates
          Jacob Goffin

          Hey Scott,

           

          What if when a person's reset date was null (meaning they are PENDING), that we insert their Cutoff Date. The calculation I created was "Reset Date (w/Nulls as Cutoff Dt)":

           

          IFNULL([Reset Date],[Cutoff Date])

           

          Then you can create a view that includes all employees, and shows completed password resets as well as the final cutoff dates for anyone who is still pending. In my view, percentage complete is just a simple ratio of a running total and a total of all employees. See attached workbook for a potential view.

           

          Hope this helps.

           

          Cheers,

          Jacob

          1 of 1 people found this helpful
          • 2. Re: Data structure for running totals with dates
            Scott Henry

            Thanks for the help Jacob! That definitely gets me a little closer to where I want to head. After banging my head for a while, I bit the bullet and restructured the data for better visualization. Definitely takes practice wrangling things into a workable format and I appreciate your help!