11 Replies Latest reply on Jul 8, 2013 8:51 AM by Luke Stoughton

    Concatenating numbers and texts

    Luke Stoughton

      Hi all,

       

      I am trying to include a measure which is a concatenation of a number of student applications (I work in education) coupled with a % increase/decrease value when compared with last year. I want the end result to look per the following, if YTD applications are at 550 and -29% down from the previous year YTD: 550 (-29%).

       

      I can get it using the STR function but the resulting calculated field is shown as a Dimension and cannot be inserted into the main table next to other columns of data as it is not a Measure. If I convert it to being a Measure it turns into a Count Distinct and makes no sense. Any ideas what I can do? It works in Qlikview and can be added to the main output table as a Measure despite being text. Any help would be appreciated.

       

      Thanks,

       

      Luke

        • 1. Re: Concatenating numbers and texts
          Dimitri.B

          I don't think this is possible, as there is no built-in Tableau function that will do that and keep it as a measure.

          But if you are building a table, why don't you just display two columns? Like this: | 550 | -29% |

          • 2. Re: Concatenating numbers and texts
            Mark Jackson

            You could drop it on the row shelf and display it in your table that way. Or you could make a mock cross tab by plotting a zero value and giving it your label. Then just format it to look like a cross tab.

            1 of 1 people found this helpful
            • 3. Re: Concatenating numbers and texts
              Jonathan Drummey

              This is a case of #1. I'm 99 44/100ths %certain that there is at least one solution, but we can't give you details without more to go on.

               

              Tableau tries to assign Dimensions and Measures based on the following logic that looks at the data type and aggregation level of the field:

               

              - row level fields that are categorical (strings, dates, booleans) are Dimensions

              - row level fields that are numeric are Measures (however we can manually change those into Dimensions)

              - all aggregated fields like a SUM(IF YEAR(Date)=2013 THEN [Number of Records] END) calculated field are Measures, regardless of whether they are categorical or numeric.

               

              When we turn a Dimension into a Measure, Tableau uses the following logic that looks at the data type of the field:

               

              - numeric fields become Measures

              - categorical fields must be aggregated, so Tableau will prompt us to assign an aggregation, such as COUNTD().

               

              One more bit that influences potential solutions:

               

              - strings and booleans are always Discrete (blue pills) that create headers when on Rows or Columns.

              - numbers and dates can be Discrete (blue pills) or Continuous (green pills) that create axes when on Rows or Columns.

               

              I'm pretty sure what is happening is that the YTD Applications Measure is a row-level field, that turns into Dimension when wrapped in STR() because it's categorical now, then when that calculated field is dragged back to the Measures section Tableau asks to aggregate it. One solution here could be to use the MIN(), MAX(), or ATTR() aggregation on the string. Also, what Mark proposed could work, or another solution could be to just have both Measures on the Text Shelf (which is way easier than using STR() to do string formatting). But without more details, I can't give you more specifics. That's why we ask for a .twbx, since that includes your data and ideally a mockup of what you are trying to do, it's a lot easier to give you useful guidance.

              1 of 1 people found this helpful
              • 4. Re: Re: Concatenating numbers and texts
                Luke Stoughton

                Thank you all for your kind replies. I have attached a twbx workbook- the tab in question is the Board Paper tab. In essence I would like to hide the 2012 Apps - YTD column and concatenate the 2013 Apps - YTD column with the Apps 2013 v 2012 one per the description in my initial question.

                 

                You are of course all correct in that I could leave this as it is, in separate columns, however this is being designed to be accessible to senior management where I work on tablets/iphones etc so space is at a premium. There are also a few more bits to add in which will take up extra space.

                 

                I was interested to see if Tableau can handle this- it isn't a problem in Qlikview or OBIEE, two other BI tools we work with.

                 

                If it turns out it isn't possible to do this in Tableau it would be a useful addition to any new releases.

                 

                Thanks again,

                 

                Luke

                1 of 1 people found this helpful
                • 5. Re: Concatenating numbers and texts
                  Mark Jackson

                  I created a few of the columns for you to show how it is done. The disadvantage is the headers are on the bottom because it is a chart and not a crosstab. But you could overcome that pretty easy with a dual axis and some formatting. Or just hide the axis headers, put it on a dashboard, and create the headers with floating text boxes.

                  1 of 1 people found this helpful
                  • 6. Re: Concatenating numbers and texts
                    Mark Jackson

                    BTW...there was no string concat / number formatting required for this solution. It simply takes advantage of the abilty to add multiple fields to a single label in v8. Since there is no TEXT() or FORMAT() function in Tableau yet, this is going to be the most simple approach.

                    • 7. Re: Concatenating numbers and texts
                      Mark Jackson

                      last thing...

                       

                      Tableau is a visualiation tool first and foremost. It helps you think differently about how the data is displayed. I'd encourage you to explore new ways of presenting the information. You'll have to put on your salesman hat though because people hate change.

                       

                      board example.png

                      1 of 1 people found this helpful
                      • 8. Re: Re: Concatenating numbers and texts
                        Luke Stoughton

                        Thanks Mark (and everyone else!) great to see other people's perspectives on these things! Plus I'm only just getting in to Tableau.

                         

                        Cheers.

                        • 9. Re: Re: Concatenating numbers and texts
                          Luke Stoughton

                          Mark, one last thing- how did you get the chart values to line up as per a table? When I try and re-create your chart I can get the text values to appear but they are staggered like they would be in a chart depending on their value. I think it must be that you set the start and end values to 0 within the axis but I can't see how to do this. Thanks.

                          • 10. Re: Re: Concatenating numbers and texts
                            Mark Jackson

                            What is being plotted is the sum of zero for each one. The fields I created are at the top of your measures list. They are calculations with a formula of "0". I just used made the labels use your original fields.

                            1 of 1 people found this helpful