4 Replies Latest reply on Dec 18, 2017 2:15 PM by Tim Whitman

    Snapshot of data "state" by week

    Tim Whitman

      I am trying to create a snapshot-style chart that shows the state of a unique record set by week. I've tried using COUNTD on a LOD with a table calculation, but can't quite figure it out. I'm pretty sure this is possible but I'm just missing something. Ideally the user could control the # of look back weeks, i.e. last 12, 16, 20, etc.


      The data is a "history" table that contains multiple entries of a record as it changes over time. Basically the intent is to show that the highest priority issues are being addressed over time.


      148636/9/16 14:28Open
      248636/9/16 14:28Open
      348636/9/16 14:39Open
      448636/9/16 14:40Open
      548636/9/16 14:40Open
      655029/13/16 13:06Open
      755029/13/16 13:06Open
      855029/13/16 13:06Open
      955029/13/16 13:07Closed
      1055349/26/16 18:56Open
      1155349/26/16 18:56Open
      1255349/26/16 18:56Open
      1355349/26/16 18:57Open
      1455349/26/16 18:57Open


      The closest I've gotten is a COUNTD with a rolling total table calculation, but that isn't quite it as there are only ~80 unique records in the set, yet the last column shows a total count over 350.




      Any help is much appreciated!

        • 1. Re: Snapshot of data "state" by week

          could you paste sample output you are expecting based on data provided...? thanks

          • 2. Re: Snapshot of data "state" by week
            Tim Whitman

            Hi Suresh -


            Here is what the chart should look like for the past 20 weeks:



            To produce this I had to assemble the record count by hand from the raw data for each weekly snapshot, but I'm hoping it is possible for Tableau to do this automagically!

            • 3. Re: Snapshot of data "state" by week
              Tim Whitman

              And here is the workbook for that one...

              • 4. Re: Snapshot of data "state" by week
                Tim Whitman

                As an additional note, here is the SQL I used to calculate the "state" for a given date column when manually creating the example chart I am trying to achieve:

                select HT1.RELATION_ID, HT1.IMPORTANCE, HT1.STATUS, HT1.MODIFIED from (
                      select * from (
                            select * from HISTORY_TABLE HT3 where HT3.MODIFIED <= '2017-12-15' order by HT3.MODIFIED desc
                      ) as HT2 group by HT2.RELATION_ID
                  ) as HT1 where HT1.STATUS != 'Closed'

                So, starting from the most inner select, first I want all records earlier than "THIS DATE" where "THIS DATE" is the value from DATETRUNC('week', MODIFIED) used as my column in the view. Then, I find the most recent record using order by and then a group by in the second select. And finally, I remove closed records from the final select to get those records that were OPEN at that time.


                I've tried wrapping my head around the FIXED, INCLUDE, and EXCLUDE LOD keywords to try and get the filtering precedence right, but I can't quite figure out how those map to the above SQL.


                Any help would be much appreciated!!


                Oh, and use the attached updated History-Mountain-v2.twbx workbook because in my haste to cleanse my data of anything sensitive I messed up the open/closed counts in the original version.