6 Replies Latest reply on Oct 10, 2014 9:01 AM by Michael Sherman

# Dual axis problems--one x axis is days, the other x axis is months. How to show them on a dual axis?

Hi users, I have a series of problems I'm trying to work out for a dashboard. Ultimately, I need to show data by days, but also need to show table calculations that summarize the day data by larger time windows.

Look at the attached "daily axis.png". What I have are bars on a daily axis also split by months. This is because relevant patterns can be seen both on a daily and monthly time scale, so the split is useful. The end user loves bars, so this isn't going to change.

With these daily bars, we would like to show some kind of summarization line -- running totals, monthly total, etc. (changeable by parameter). In "daily axis.png" the black lines are running monthly totals. This is not really what we want though--we want just a line with one point in each month showing the monthly total. Something like in the attached "monthly axis.png".

The closest I've been able to get was taking a window max of the monthly running total (which gave me a line across each month), and then doing an if (index() = 15) to just show one point in the middle of the month. But then I can't get these points to join together in a line.

Any advice is appreciated. Ultimately the goal here is to use a parameter to change the table calculation from monthly total, to a monthly running average, to a yearly running average, etc., all as a point of reference when examining the bars.

Thanks, -Mike

• ###### 1. Re: Dual axis problems--one x axis is days, the other x axis is months. How to show them on a dual axis?

Hi Michael,

Take a look at the attached workbook and let me know if this is what you are looking for.

Thanks,

Deepthi

1 of 1 people found this helpful
• ###### 2. Re: Dual axis problems--one x axis is days, the other x axis is months. How to show them on a dual axis?

Hi Michael,

if you have your parameter control and associated calculated fields in place,  you just need to use the dual axis option and keep both the "day" aggregation and "month" aggregation as continuous. Screen shot attached. I hope this helps.

PS -I have filtered the months to four months to reduce cluttering. 1 of 1 people found this helpful
• ###### 3. Re: Dual axis problems--one x axis is days, the other x axis is months. How to show them on a dual axis?

Deepthi, thank you very much. This is not exactly what I was looking for but it is close--the big difference is that we would prefer if the bars were really each just a point, and that the points were connected by a line. I need a day or so to process all of this and look through it and I'll get back to you. Thank you!

• ###### 4. Re: Dual axis problems--one x axis is days, the other x axis is months. How to show them on a dual axis?

Radha, thank you. That worked very well for monthly total.

But what I would like to do is to change the line from monthly total to something else. Check this out: The top graph is based on what you said, and it works perfectly--daily bars, line with monthly totals. The bottom graph is a moving average table calculation. What I would like to do is to take the line (months) from the moving average on the bottom and have a parameter (or some kind of dashboard control) that lets me replace the line on the top graph with the line from the bottom graph of moving average (or any other field--like running total by year, for example). Any ideas?

Thank you!

• ###### 5. Re: Dual axis problems--one x axis is days, the other x axis is months. How to show them on a dual axis?

Hi Michael

Try the following solution

Create the required parameter containing the names of the calculations.

Then create a calculated field where you include an 'if' condition based on the parameter values-

for example

if(<parameter value> = "moving average") then <table calculation function> else if ..... and so on and include this field on to your rows.

The thing to understand here is that , table calculations are also functions at the end of the day. You can find the list of table calc functions available in the link below.

Table Calculation Functions

Do reach out if there is anything .

• ###### 6. Re: Dual axis problems--one x axis is days, the other x axis is months. How to show them on a dual axis?

Thanks for your response Radha. I understood that aspect of the problem--writing the table functions out and creating a field with a CASE/IF statement that checks the value of a parameter.

The part I didn't understand was that Tableau was smart enough to change the "repeating every" part of the table calculation (which isn't part of the written calculation). So now I am able to switch between yearly running totals (repeating every year) and monthly running totals (repeating every month). That was my hangup and thank you for your answer. I should have had faith .

But there is still one more problem. Monthly totals (or yearly totals) look like this when using a table calculation while also showing the days (this is similar to what is in Deepthi Pullarkat answer workbook):

Code is WINDOW_MAX(RUNNING_SUM(SUM([Amount])))

I want to replace these lines from the table calculation with a single point for each month joined together in a line graph (to look like what you posted first). I understand why the lines are showing--Tableau is doing the table calculation for every day, which results in a line. I can change the code to:

IF(index() = 15) THEN WINDOW_MAX(RUNNING_SUM(SUM([Amount]))) ELSE NULL END

Which gives:

What I am missing now is the ability to connect these lines. I know if I follow your first answer I can get this as lines, but then I lose the ability to do Table calculations like running totals and moving averages.

Any thoughts on this?

Thank you so much Radha!