1 2 Previous Next 29 Replies Latest reply on Sep 21, 2016 12:00 PM by Austen Robinson

# Table Calc Question

In the attached Workbook I did a table calc for the percentage change and used date.  What I am really after is just the percent change in units and not all the dollars.  Also, I need to do some sort of calculation to switch and show years.  So 2015-01 thru 2015-12.  I assume you have to split the year from the month some how but not quite sure how to do it.  Any help is appreciated in advance.

• ###### 1. Re: Table Calc Question

Well here's a start.

I did "Edit in shelf" on the quick table calc you had.  That lets me cut-and-paste the tableau-generated text of the calc into my own calc.  Then I modified it to act only if you're looking at Units.

However, Tableau is still going to carve out the space for the calc value for all {Category] values.  it's just blank for all the rest.

I'm playing with something though.  Stand by.

• ###### 2. Re: Table Calc Question

Thanks Joe.  I just looked.  Looks great.  Ill check back when I get home to see if you have gotten any further.  Thanks for the help.

• ###### 3. Re: Table Calc Question

OK.  In Sheet 4 I tried a hack that I thought would work.

You can insert a carriage-return in a string calc simply by typing it into the text of the calc.  See Calculation 2.  I told it to load nothing into the string if Calc1 was null (meaning Category is not UNITS.)  And if Calc1 is not null, then load a carriage return plus the string value of the calc itself.

Then I inserted those two variables on the text shelf side-by-side.  See Sheet 4.  My hope was that we would force a second line only for "UNITS".

Well it did what I told it to do, but I still needed to expand the width of the row to accommodate the 2-line value.  And by expanding that row, Tableau expands all rows.  So we end up with the same viz in the end.  C'est la vie.  It's gonna look like that.  Go with the original sheet and forget about Calculation 2.

So for your second question about selecting years, see the "Unit Churn by year" sheet.  Since your date is a character string, I created a YEAR parameter, and then I created a calc to select by the value of that parameter.  The MID() function is the equivalent of a SUBSTRING function in other tools.

I needed to change the formatting of the sheet from "Fit Width" to "Standard" because when you go to 2015, the number of columns increase, and "Fit Width" shrinks column width to squeeze all columns onto the sheet without a scroll bar.  And that resulted in a lot of the values being displayed as ####### if they wouldn't fit in the shrunken columns.

• ###### 4. Re: Table Calc Question

One other thing about selecting years in the :"by year" sheet.  Edit the title.  I inserted the parameter into the title so that it reflects the year selected.

• ###### 5. Re: Table Calc Question

Joe,

Thanks for that.  I think this is what fits my needs.

• ###### 6. Re: Table Calc Question

How do I get the text at the bottom off of the filter below?

• ###### 7. Re: Table Calc Question

also I was able to format the first sheet to show % but the subsequent sheets I cannot.

• ###### 8. Re: Table Calc Question

The "Limit Top 12..." text comes from a condition you have put on the DATE filter on your UNIT CHURN sheet.  But if you go with the Parameter solution I offered, the question is moot.

Still, here is how that text gets there.  Edit the DATE filter.  There are tabs across the top of the edit-filter dialog box.  One is "Top".  click that.  You have selected the "By field" option in there.  If you want to get rid of that, click NONE and save.

• ###### 9. Re: Table Calc Question

To make the value of [Calculation 1] display as a percent on all sheets, right click on [Calculation 1] in the measures list.  About two thirds down the list of options is "Default Properties", and in the pull-down for that is "Number formats".  Select that, and then choose "Percentage" as the default property.  It will carry to all sheets.

• ###### 10. Re: Table Calc Question

You can set the display format on a sheet-by-sheet basis too, but for this field, you probably always want the display format to be percent.

• ###### 11. Re: Table Calc Question

in the sheet where calculation 2 is you said disregard that.  So i took it out.  now i need to put calculation1 in its place so it is like the other sheets.  Well I dragged over calc 1 to the marks but i dont see the calculation.  however the other problems are solved.  Thanks.

• ###### 12. Re: Table Calc Question

I just took out [Calculation 2] entirely, and the sheet that used it.

The "Unit Churn" sheet has Calculation 1 in it already.

The "Unit Churn by year" sheet has the year filter set up using the parameter.

And I set up the default formatting on Calculation 1.

(I edited this reply to add the new version of the workbook...)

• ###### 13. Re: Table Calc Question

Thanks Joe....That solved all my problems...well thats not true.  I have a similar excel workbook that I need to do the same thing with.  The format is slightly different.  Ideally I would like to add this excel workbook to this tableau workbook as another data source but the last time i tried it it messed up my other worksheets.

What is the correct way to add the other data set (excel workbook) without screwing with what I already have?

• ###### 14. Re: Table Calc Question

In the upper left of your Tableau Desktop workbook, you see "File, Data, Workbook, ..."  Click DATA, and choose "New Data Source".  In the pop-up window select "Excel".  That will put you into an explorer window that will let you select your excel file.  I followed that step and loaded in a bogus sheet called "Cars".  It's in the attached.

Now you can make sheets that uses the CARS data source.  You can blend it with your existing data source (well, except that there's nothing in common to blend on...)

1 2 Previous Next