7 Replies Latest reply on Dec 3, 2016 4:32 AM by Michael Carpenter

    Charting total scores that vary over time

    Michael Carpenter

      I have a dataset that tracks when people's scores change E.g in the set below (also attached) Pers1 score starts at 5 on 9th Feb, goes down to 4 on 7th March etc.

       

      I want to chart the total score across all people broken down by a dimension ('Type' in the test data below). I think I could do this in excel with a SUMIFS formula running down a table with all dates in, but wonder if Tableau can do this sort of thing.

       

      Any thoughts?

       

      Michael

       

      PS dates below are in Uk format dd/mm/yyy

       

          

      IDTypeDateScore
      Pers1A09/02/20165
      Pers1A07/03/20164
      Pers1A08/03/20162
      Pers1A01/04/20167
      Pers1A08/09/20165
      Pers2B23/02/20165
      Pers2B31/08/20166
      Pers2B16/11/20169
      Pers3A17/01/20168
      Pers3A07/03/201610
      Pers3A17/07/201610
      Pers3A17/10/20167
      Pers4A30/01/20164
      Pers4A15/02/20169
      Pers4A22/02/20167
      Pers4A25/03/20169
      Pers4A11/04/20164
      Pers4A03/05/20169
      Pers4A06/09/20161
      Pers4A03/10/20164
      Pers4A25/10/20168
      Pers5B08/01/20163
      Pers5B14/05/20164
      Pers5B22/06/20169
      Pers5B25/09/20163
      Pers5B31/10/20164
      Pers5B17/11/201610
        • 1. Re: Charting total scores that vary over time
          Simon Runc

          hi Michael,

           

          So yes Tableau is very good at aggregating data over time...and we can even do this without writing a single formula!!

           

          So if I drag Score onto Rows, the default aggregation is SUM, which is what we want (although we could change this to AVG, MIN...etc.). As I want a value per type, I then drag the type onto the colour shelf, to get a line per type

          I then drag the date onto Columns (I've set to day level, but we can view this by week, month, quarter, year...)

           

          So just 3 drag and drops and about 10 seconds work!!

           

          Hopefully I've understood what you need, and that it makes sense, but let me know if not.

          • 3. Re: Charting total scores that vary over time
            Michael Carpenter

            Hi Simon

             

            Thanks for having a look at this.

             

            That's not quite what I'm after - I've attached an excel version. The problem is that the person's score stays the same up to each changes - e.g. see Person 2 in the attached (which I've tweaked so they are the only one of Type B so you can see them in the chart) - their score starts at 0, then goes to 5 on 23/2 then goes to 6 on 31/8 then goes to 9 on 16/11. In between it stays the same - it doesn't go to zero, which I think is what happens in your version.

             

            My guess is that I need to create a list of dates (like in column H in the attached) and use a function that does something like what I'm doing in Excel?

             

            Michael

            • 4. Re: Charting total scores that vary over time
              Michael Carpenter

              Hi Luciano

               

              Thanks for having a look - I don't think that's quite it - see my response to Simon's post

               

              Michael

              • 5. Re: Charting total scores that vary over time
                Simon Runc

                Oh boy!!...so this *can* be done in Tableau, but we are moving from "10 seconds work, what Tableau was designed for" to some pretty advanced features...How we do this is to trigger, what is known as "Data Densification" in Tableau. This means that Tableau can create all the dates in-between the dates you have in your data. He's a video on the subject Tableau Request Live - Data Densification on Vimeo

                 

                This aspect of Tableau is not-well documented (by Tableau), and so people like Jonathan Drummey, Joe Mako...and others have experimented to help our understanding of when densification is triggered. btw as an alternative, as you've alluded to, you can just do this in the data (so create a row for every date/person combination), and then we're back to the 10 second version!!

                 

                So here goes....

                 

                One of the forms of data densification is called "domain padding". This is a feature, where "Range Aware" pills can pad out the data. This only works on "Range Aware" pills, which are bins and dates (fields where Tableau knows the Min and Max and the increment). We turn this on by ticking "Show missing values"

                 

                 

                So now we have every date. Now we can only access these "padded" rows with a Table Calc. I've done this in 3 stages so you can see what's going on.

                 

                So first I create the following Table Calc

                [Score - Dense]

                IF ISNULL(SUM([Score])) THEN PREVIOUS_VALUE(SUM([Score])) ELSE SUM([Score]) END

                 

                and set the compute using to "Date"

                This is the first chart on the "Densification - Working" tab. I've also got ID in the vizLoD (viz Level of Detail).

                 

                Next part is to add up each one for each particular date...this involved another advanced feature (nested Table Calcs)

                [Score Dense - Window Sum]

                WINDOW_SUM([Score - Dense])

                 

                So when we have a nested Table Calc, as we do here...we can set each up to run differently. So I set this one as follows

                Score Dense is Compute Using = Date (as before). The red outlined bit is where you can change which Table Calculation you are setting up in a nested situation

                but the WINDOW_SUM needs runnning over each ID, so they get added together.

                I've also now brought Type onto the Colour Shelf (this adds it to the Viz LoD) and as I'm running my table calculations by Date and ID, Type is the partition (in human speak...the table calcs start again for each Type)

                 

                Now this looks pretty good

                 

                However, as we have ID in the vizLoD we are actually plotting multiple lines, it's just that the way we've calculated them they are on-top of one another...so off to another trick!

                 

                to only return a single value per Date/Type, I then use this formula

                [Score Dense - Window Sum First()=0]

                IF FIRST()=0 THEN [Score Dense - Window Sum] END

                 

                So this just says only plot the First mark for each partition. As you can see I've nested the  [Score Dense - Window Sum] in this calc, so we have a triple nest! This one we set up compute using = ID (and the other 2 as before)

                 

                 

                But we have a problem...we now have gaps...so we need to invoke more densification (this time not a feature, as such, but something Jonathan/Joe and others have worked out and is well explained in the Video link above)

                 

                 

                So I bring another table calc...just index() onto the detail shelf and this triggers the marks we need. And we set up as follows

                and after all that...voila!

                 

                So that takes you from "10 seconds, super easy" to "Jedi level Tableau"...

                 

                Hope this makes some sense, but if you can do the densification in the data it'll make your life much easier.

                 

                ...Ended up being a pretty fun challenge! Also let me know if it's not quite calculating as expected.

                2 of 2 people found this helpful
                • 6. Re: Charting total scores that vary over time
                  Luciano Vasconcelos

                  I hadn't seen Simon's answer.

                  You are in good hands.

                  • 7. Re: Charting total scores that vary over time
                    Michael Carpenter

                    Wow - thanks for putting your answer together - it looks like it's doing what I was after.

                     

                    It will take me a bit of time to work through the detail - I'll get back to you if I have any problems

                     

                    Thanks again

                     

                    Michael