8 Replies Latest reply on Feb 25, 2015 11:47 AM by hannah.johnson

# Filter out data in chart from only 1 of 2 sources

In the attached workbook I need to filter out "actual" data but still show "planned" data. I only want to show January real data but since the database is a server and updated weekly I cannot just refresh on the end of a month. I also want to still show an entire year of planned data in the same view.

I think this can be done with a parameter but not sure how to do that. Also if there is a way to automate the parameter data that would be great because there are over 15 graphs similar to this in the report.

Thanks!

• ###### 1. Re: Filter out data in chart from only 1 of 2 sources

To clarify, you want to keep the orange "planned" data as well as the January green "actual" data, but leave out the February green "actual" data? Are you looking to include the actual data once the month is over?

• ###### 2. Re: Filter out data in chart from only 1 of 2 sources

Yes, so for this month I only want to show January green and all orange. Next month I will want to show Jan and Feb green and all orange.

Thank you

• ###### 3. Re: Filter out data in chart from only 1 of 2 sources

I used this calculated field to filter out the current month. So the current month's total (which is currently February), will not be shown until the next month is started.

IF MONTH([Month]) >= MONTH(TODAY())

THEN NULL

ELSE [Total]

END

This checks if the month number of the [Month] dimension is equal to the month number of today's date. If the month number of the [Month] dimension is greater than or equal to today's month number, then show no data, else show the total.

After you create the new calculated field with the equation listed above, replace the SUM(Total) Measure on Rows with the new calculated field. Now only January should be showing. Once it is March, the February actual total should show.

-Hannah @ Tableau

• ###### 4. Re: Filter out data in chart from only 1 of 2 sources

Thank you so much!

• ###### 5. Re: Filter out data in chart from only 1 of 2 sources

Follow up question. I also need to apply this to a cpcall graph and whenever i try to apply the formula is says you cannot mix aggregate and non aggregate comparisons in an if statement. any suggestions?

• ###### 6. Re: Filter out data in chart from only 1 of 2 sources

Hmmm I'd need to see the .twbx file to find out which measures are aggregated or disaggregated. Most likely there is a measure in the calculation listed as [Sales] rather than SUM([Sales]), as an example. There is probably a disaggregated value which needs to be aggregated or there is an aggregated value which can't be aggregated again. Calculations need to use all aggregated values or all disaggregated values, since Tableau can't calculate both together.

http://onlinehelp.tableau.com/current/pro/online/windows/en-us/help.htm#calculations_calculatedfields_aggregate_create.h…

- Hannah @ Tableau

• ###### 7. Re: Filter out data in chart from only 1 of 2 sources

I've included the formula I need help with in the second attachment. It's on the second tab and the formula is calculated as an aggregate.

Thank you

• ###### 8. Re: Filter out data in chart from only 1 of 2 sources

If I understand correctly, you want to use the same formula as above, where it's only showing months which have passed. So currently you want only January to show for this new calculation (sum([Spend])/sum([Calls]))?

In order to show the new calculation for only January (or for months previous to the current month), the same Calculation will be used.

IF SUM(MONTH([Month])) >= SUM(MONTH(TODAY()))

THEN NULL

ELSE SUM([Spend])/SUM([Calls])

END

The reason the formula said you cannot mix aggregate and non aggregate comparisons in an if statement is because the Month to Month comparison was not aggregated. Do fix this, SUM was added to both of the Month comparisons.

- Hannah @ Tableau