...nice challenge! In short yes you can using table calcs. In the attached I've created the 2 new fields, although notice that my Category one for 2013 terms them all 'New Biz' as it can't find an occurrence of them in 2012 (so I think this is actually more accurate , as your one terms every one in 2013 Renewed, on the basis they are in 2013...self-fulfilling prophecy!!). However you could easily add an extra if to account for this using the FIRST() call.
Hope how I've done it makes sense. I'm running each LOOKUP on year, and asking it to compare to the previous (-1).
If you want a more detailed explanation, let me know.
Look Up Table.twbx 36.4 KB
Hi Simon, wow super quick thank you & right about the "newBiz" - it has to be new at some point.
Could you please try to re-load the file, I would like to take a look but getting an error when opening it using 8.1
ah I only have 8.3, and Tableau has no back compatibility. I've amended the XML (a bit naughty!) to think it's a 8.1 file (none of the functionality I've used is 8.3 specific), and then re-zipped, so it should work (all a twbx is a zipped folder containing the workbook and the data). Let me know if you can't open it, and I'll send over each calculation, so you can re-create it..
Look Up Table.zip 35.3 KB
Hi Simon, hope you have some patience left as I was unable to open the file - I relabled the .zip to .twbx (cannot connect to database - invalid database name) also tried to run the .twb file (inside teh zip) and in both cases got errors.
Would you pleases end over the calcs instead? Just to clarify, does this method create new Dimension, which one can then use in subsequent calcs?
Thanks for your interest and help on this one - I've been stuck on it for a while,
No problem. Here is a view of the output
The 2 new Formulas Tableau Original Office and Category Tableau, are as follows
Tableau Original Office
IF ISNULL(LOOKUP(ATTR([Branch]),-1)) THEN ATTR([Branch])
ELSEIF ATTR([Branch]) = LOOKUP(ATTR([Branch]),-1) THEN ATTR([Branch]) ELSE LOOKUP(ATTR([Branch]),-1)
IF ISNULL(LOOKUP(ATTR([Client]),-1)) THEN 'New Biz'
ELSEIF ATTR([Branch]) = [Original Office Tableau] THEN 'renewed'
As these are Table Calcs the Partition and Addressing needs to be set to look back 1 year, and compare the values
I set eaxch Table Calc up the same. I used advanced, and set like this
This means it's restarting every customer. as I'm not interested in Brand, Excel Category or Excel Original Office (they just happen to be fields in the Viz), in the the next screen I've set up as follows
by Only going down as deep as Year, I get the desired affect. Hope this all makes sense
In terms of them being Dimensions you can use, it's a yes and no! You can use them, but any Viz would have to have Year and Client in the view (or detail shelf) so the calculations can work. Table calculation work over the viz, and not 'in the background' as Row Level calcs do. This is the same for aggregate calculations.
Let me know if this is confusing and I'll direct you towards some good articles explaining it (and a lot better than me!!).
Also see Find Earliest/First/Least and/or Latest/Last/Maximum of a Field and Use that for Other Fields. Tableau devs definitely know about this as an issue.
Also, there's a possible solution for this particular thread involving a modified self-data blend, where the blend is on the year but one data source offsets the year by one. This lets you get the prior year's information as a dimension or measure in the view (depending on exactly what you are trying to do) and avoid some of the complexities of table calculations.
Thanks Simon - that solves the problem though have to say it really wasn't obvious! WoW!
Would be good to be able to do this in the background, hopefully some clues in Jonathan's post
I hadn't thought of that solution from Jonathan Drummey...very nifty (but again Data Blended fields can be restrictive, depending on what you are doing, eg. measures always act as aggregated fields)
Although wouldn't exclusively solve your issue, you could vote this idea up http://community.tableau.com/ideas/2177 which is basically allowing for aggregated calculation that operate regardless of the detail in the Viz (very much like Row Level calculations).
What I've found, is that sometimes it's easier to do some back end data-reshaping/creation-of-aggregated-fields outside Tableau, and let Tableau do what it does best, Visualize. I've found that nearly everything is possible, but sometimes a bit of upfront work saves time (and trouble) in the long run.
Here is a Link, with a load of other links! which take you through Table Calcs from Beginner to Zen!