
1. Re: combining a bar chart and a line graph
Andy Cotgreave Aug 26, 2011 1:53 AM (in response to amberherczeg0)I've attached one way of doing it. I created a parameter for Year of Sales. Then I created two calc fields  one for Sales in the same year as the selected parameter. The other is for the Sales in the year before the Parameter value. The line and bar show sales for Year and Previous Year.
This is quick and dirty  there's probably a better way of doing it.
Andy

2. Re: combining a bar chart and a line graph
Joe Mako Sep 10, 2011 9:35 AM (in response to amberherczeg0)here is a slight modification of Andy's with the extra marks not displayed. I did this by changing the formulas to an IF/THEN/END instead of the IFF() function, so Null is returned when the condition is not True. I then formatted the measure pills so that Special Values (Null) is hidden in the Pane. Also I think it is important the the Dual Axis be synchronized, so the view is not misleading, and you can make comparisons between he years.

3. Re: combining a bar chart and a line graph
jaredhislop2 Oct 31, 2011 2:15 PM (in response to amberherczeg0)What if I wanted this same effect but I didn't not want the use of the parameter?
Also, I would like to show it by week for the last 52 weeks and the 52 weeks prior to that over top (as the line)?
example: if today is week 32 of 2011. then the bars would be through week 33 of last year, 2010. Then the line would be week 32 of last year, 2010 through week 33 of the year prior, 2009.

4. Re: combining a bar chart and a line graph
Joe Mako Oct 31, 2011 2:56 PM (in response to amberherczeg0)Jared, without additional specific details on your business logic for dealing with weeks, that may be a deceptively hard question as there are a number of interesting ways to deal with weeks in Tableau. The way weeks are handled can change between years, and Tableau does not have built in way to normalize to the week because some yeas have 52 and others 53. It may not be meaningful to compare week 28 of 2010 to week 28 of 2011.
If you have a retail calendar, or your ISO week numbers, you want just want to use that by adding them in as additional fields in your data instead of Tableau's date functions.
see http://www.clearlyandsimply.com/clearly_and_simply/2009/04/weekinweekouttableau.html for some more details.
Do you have any specific business logic when it comes to dealing with weeks?

5. Re: combining a bar chart and a line graph
jaredhislop2 Oct 31, 2011 3:11 PM (in response to amberherczeg0)That is what my client wants. They would like to compare the rolling last 52 weeks with the 52 weeks previous in a line chart, showing previous 52 weeks, over bar chart, showing the last 52 weeks. I understand what you and the article you sent means by the weeks not lining up completely, but is there any sort of work around?

6. Re: combining a bar chart and a line graph
jaredhislop2 Nov 1, 2011 8:04 AM (in response to amberherczeg0)That is what my client wants. They would like to compare the rolling last 52 weeks with the 52 weeks previous in a line chart, showing previous 52 weeks, over bar chart, showing the last 52 weeks. I understand what you and the article you sent means by the weeks not lining up completely, but is there any sort of work around?

7. Re: combining a bar chart and a line graph
jaredhislop2 Nov 1, 2011 1:01 PM (in response to amberherczeg0)What about a formula for the last 12 months. I understand that weeks might line up but that shouldn't be a problem with the last 12 months. Is there a formula for the last 12 months and previous 12 months where I can get the value of each to overlap in the same view. something like:
Last 52 Weeks
sum(
IF DATEDIFF('month',[Date Starting Monday],TODAY())<=12
AND
DATEDIFF('month',[Date Starting Monday],TODAY())>0
THEN [Value]
END)
52 Weeks Prior
sum(
IF DATEDIFF('month',[Date Starting Monday],TODAY())>=12
AND
DATEDIFF('month',[Date Starting Monday],TODAY())>0
AND
DATEPART('year',[Date])=DATEPART('year',DATEADD('week',1,TODAY()))1
THEN [Value]
END)
those formulas won't allow them to overlap, see picture attached.

nonoverlapping.jpg 53.2 KB


8. Re: combining a bar chart and a line graph
jaredhislop2 Nov 1, 2011 1:02 PM (in response to amberherczeg0)see next comment

9. Re: combining a bar chart and a line graph
Joe Mako Nov 13, 2011 3:33 PM (in response to amberherczeg0)What if you use a calc field with a formula like
DATENAME('month',[Date Starting Monday])
on your columns shelf instead of the pill MONTH(Date Starting Monday)

10. Re: combining a bar chart and a line graph
leroux vincent Jan 23, 2012 5:56 AM (in response to amberherczeg0)Hi Joe,
I downloaded your file year_and_previous_year_sales_by_week_edit.twbx and the result is impressive.
I am trying to put in the same graph budget (line), last year(bar) and actual sales(bar) figures with differetn mark and I don't manage to do it.
Attached is waht I get and I don't find the trick to put them together. When I drag and drop the measure in the axis then I can only get line or bars.
Could you give me your trick to get your result.
Thanks;

problem.jpg 214.9 KB


11. Re: combining a bar chart and a line graph
Joe Mako Jan 23, 2012 6:31 PM (in response to amberherczeg0)I think what you are looking for is a dual axis multiple mark type (combo chart) view, as in the attached.
see http://www.tableausoftware.com/learn/tutorials/ondemand/buildingviews for information on dual axis and combo charts

dual_axis_multi_mark_type.twbx 17.3 KB


12. Re: combining a bar chart and a line graph
leroux vincent Jan 24, 2012 3:46 PM (in response to amberherczeg0)Thanks a lot Joe. Very helpful.

13. Re: combining a bar chart and a line graph
amberherczeg0 Apr 13, 2012 7:49 AM (in response to Joe Mako)Joe  is there a reason the if/then/end formula doesn't work for aggregate functions, too?

14. Re: combining a bar chart and a line graph
Cristina Palo Jul 27, 2012 6:55 AM (in response to Joe Mako)Joe
I have been trying to figured this out for a couple days now and just ran accross your posting here! I read the
http://www.clearlyandsimply.com/clearly_and_simply/2009/04/weekinweekouttableau.html
and understand I need to do some work here to fix my data. However I am new to all these and cant figure out what formula I need to use in order to mirrow my clients calendar
They did not have a week 53 in 2010, rather they called week 1 the week from12/26/201001/01/2011. Therefore their 2011 52nd week is week from 12/18/201112/24/2011.
2012 week 1 starts 12/25/201112/31/2011....I know it sounds a little crazy but this is how they are looking at their weeks.
Can you help me with the formula so Tableau gives me the proper weeks?
Thanks a lot,
Cristina