4 Replies Latest reply on Feb 8, 2016 9:48 AM by alex.beuhring

    Trending monthly client status based on multiple update time stamps.

    alex.beuhring

      I have a request to provide monthly trending by number of clients that fall into a particular status based on the first day of the month.  Client statuses are updated at random intervals by multiple teams, however I need to show a trend over months of a year based on what the status was on the 1st.

        • 1. Re: Trending monthly client status based on multiple update time stamps.
          Adam Crahen

          Hi Alex-

           

          This is kind of hacky.  There might be a better way, but here is what I came up with.

           

          2016-02-01_22-55-52.png

          2016-02-01_22-59-34.png

          That Status Calc is pulling the status as of the 1st of the month.

           

          Delta examples:

          4/2 updated to risk - Status Calc pulls last status of challenged from March

          10/22 updated to challenged - Status Calc pulls last status of risk from September

          12/7 updated to good - Status Calc pulls last status from dec 1st of challenged

           

          Here is the downside, the detail needs to be in the view for this to work because I of table calcs to pull previous values.  I don't think you can do this with LODs.

           

          The "bar chart" are actually individual squares stacked.  You could turn off the borders for a more single bar view. The monthly totals are computed reference line values by cell.

           

          9.2.2 workbook attached.

          • 2. Re: Trending monthly client status based on multiple update time stamps.
            alex.beuhring

            Wow.  I appreciate the level of effort Adam!  I had to get the 9.2.2 installed to take a look.  I really like the visualization and it will work well for the trending I am trying to accomplish, however there were a couple issues with data validation listed below.

            1.  In the sample sheet there are only 17 clients, however the visualization shows 20 for January.(ie)

            2.  In the calculation, it seems that it is only reporting for clients that have had an update for that month.  Today is Feb 2nd, but theres not a column for February that shows all 17 of the account statuses reflected as the were set in January.  It may be that the month should be driven off of Now() or Today().? 

             

            Im still trying to follow all the logic, but have been able to reproduce the report with active data.  Thanks again for your help.  Looking forward to hearing back.

            • 3. Re: Trending monthly client status based on multiple update time stamps.
              Adam Crahen

              Hey Alex-

               

              Glad it was some help.  I see what you are saying, you might need to play with it and tweak some things.

               

              1)  I was trying to get you a total on each bar there, but it looks like it is adding up each name/date/status combo.  Juliet is both records in the JAN 2016 Jeopardy bucket because there are different dates of update and statuses.

               

              2)  That is the hardest thing with the data set is that the date density is pretty sparse, so I had to do all those table calcs to pull prior values.  There is no Feb 16 bar because there was no Feb 16 data in the data of updates.  You can try and pad that with a data blend or something like that.

               

              Let me know if you figure it out.  Sounds like you already had some ideas to improve it.

              • 4. Re: Trending monthly client status based on multiple update time stamps.
                alex.beuhring

                Working on this throughout the weekend and still no luck....

                 

                1.  I tried to modify this calculation, then it completely breaks, putting me back at the beginning.  Think this is because the CNTD(Client Name) vs. the combination of CNTD(Client Name and Status).

                2.  I have tried multiple ways and viewed may threads on this date topic.  I really want my trend line to show the range of (from Today()  to Today() - 365 days) not date from my data.  It would be nice if tableau allowed a parameter to accept the Today() function with the date range that could be used in a discrete pill for trending.  Seems like this should be an easy item to accomplish and I'm surprised that this request doesn't come up more often.  I would think that lots of people would want to specify date range off the current date the report is pulled.      

                 

                My larger concern is #2.  The answer to it would be very helpful to pull real-time results based on current time/date.