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.
Changing Scores over Time.twbx 25.2 KB
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?
test data2.xlsx 17.3 KB
Thanks for having a look - I don't think that's quite it - see my response to Simon's post
2 of 2 people found this helpful
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.
I hadn't seen Simon's answer.
You are in good hands.
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