Product Tour
forums
Use these forums to ask questions and discuss Tableau.
A tough one
Posted August 20th, 2008 by rblaiklock
Hi all,
Not sure how to do this one ...
I have a db with fields Start Month, End Month, Value per Month.
What I want is a table /graph with the sum of the values by applicable month. To do this one record would effectively create multiple virtual data points which are then graphed.
Perhaps an example would help
Record 1: Start = Jan, End = April, Val = 10
Record 2: Start = March, End = June, Val = 20
My output would be
Jan - 10
Feb - 10
March - 30
April - 30
May - 20
June - 20
Any ideas?
thx
...Rick
Subscribe to the comments on "A tough one"
Comments
Hi Rick,
Can you share what type of database you are using? You're probably going to have to write some complex calculations and the available functions are often dependent on the type of database.
Thanks,
Erin
Rick,
This is something you can do in Excel using array formulas (also called CSE functions because you have to press ctrl-shft-enter to make them work.)
Assuming you're okay importing and using Excel, array functions are very powerful and allow you to manipulate data ranges and post results to one cell.
I've attached an Excel workbook that I believe does what you want. Realize that when you edit this formulas you need to press ctrl-shift-enter.
I don't know how practicable it is to do this type of thing in Tableau, but when I think of virtual data points I think of Excel array formulas.
Steve
If you are not using excel, then this can be accomplished by joining in a table that contains all the months. You will need to add a join clause that is something like
[start date]<=[calendar date] and
[end date]>=[calendar date]
This will duplicate the row of data for every month that it was valid. Note that in order for the database to be able to compare the dates, they need to be dates or month numbers, databases don't know that Jan comes before Feb.
Marc
Hi Erin, Steve and Marc
I'm pulling the data out of Access.
I already have a spreadsheet that does what I want.
I'm wondering if I can do it directly in Tableau to post on a dashboard, without the intermediate step of updating the spreadsheet.
It's a brute force approach - I have columns for each month and determine if that month is in the valid range for the record, if so, fill the cell with the appropriate value. Then I just add the columns to get the total for that month..
That being said Steve has created a cool spreadsheet - I hadn't thought of using array formulas.
Mark - I'll try your approach and see what I can come up with.
Thanks to all!
...Rick
Rick,
When all you have is a hammer, everything looks like a nail...
I do tend to "drop back" to Excel if at first I'm not able to get Tableau to cooperate. What I like so much about using Excel (or any office app) is that the whole thing can be automated so you don't have to do some manual process every time you want to update.
(I know that Marc is at least lurking so this is my chance to champion some automation capability in Tableau...)
So... I suspect you can link to the data directly in Excel or write a simple script that would do everything for you.
Steve
Rick,
In Tableau directly you could create 12 calculated fields (one for each month) that are of the form:
IF (start <= Mon AND Mon <= End, Val, 0)
Then you could use the Measure Names/ Measure Values fields to create the table you are looking for.
Attached is a workbook with an example of how this can be done. It's a packaged workbook so to see how the data source was set up, just right-click the workbook file and select Unpackage.
Erin,
I like this a lot (and props for dealing with the text labels that aren't dates).
The only downside is that you have to display all the rows to get to the totals at the bottom.
BTW, there's another thread on the forum having to do with hiding columns that doesn't address this problem, but it is still really cool...
Steve
Erin
Perfect - That's just what I want!!
...Rick