6 Replies Latest reply on Dec 16, 2016 7:57 AM by Maciek La

    Combining data from 2 columns, only for selected data.

    Kerry Evert

      First, I am new to tableau, so apologies if this is really simple.

       

      I have data in a sql table which has been loaded really stupidly:

      one metric is loaded with each department separately (all in 1 column):

      metricA_dept01

      metricA_dept02

      metricA_dept03

      for these metrics there the department column is a null.

       

      But another metric which I need to compare with the above data (or at least use in the same graphs etc) has been loaded with a separate column for the department:

      metricB

      with departments 01, 02 and 03 in a separate field in the data (department column).

       

      I can't use a filter on the graph with the 2 metrics, because then metricA doesn't show

       

      How do I create a calculated field to make the equivalent of metricB_dept01, metricB_dept02 etc.

        • 1. Re: Combining data from 2 columns, only for selected data.
          Matthew Risley

          Hey, Kerry! Welcome to Tableau.

           

          I think I understand what you are dealing with- but if you could take a snapshot of how the data is currently formatted vs. how you would like it to be formatted that may help us, help you!

           

          It sounds like one of your tables is formatted correctly, while the other is not. Is that correct?

          • 2. Re: Combining data from 2 columns, only for selected data.
            Kerry Evert

            Hi,

             

            Thanks for your response. The data is all in the same table. The way that the table has been structure is that they have added the metric names in a separate table, which is referenced with a key. This brings back the metric name (the measure in the screen shot below).

             

            I can't show the data, because it is sensitive, but these are the measures:

            I have created a calculated field for All Emergency Admissions, which is simply the sum of the bottom 4 measures.

            Above those is ae_attendances (essentially the same as my calculated field for All Emergency Admissions) which has a 01, 02 and 03 in the "department" column, which shows which "type" it is, as seen at the end of the bottom 4 measures.

             

            Because these are blank in the "department" column, if I use a filter, it will never show both measures on the same graph.

             

            Does this help to explain it? I'm struggling

             

            This is the data table (well, a small selection of the columns) that column with "Type 1" is the department column.

            This is the Emergency Admissions section:

            • 3. Re: Combining data from 2 columns, only for selected data.
              Maciek La

              Hi Kerry,

              It all depends what is your true data structure

              if you have something like that:

              then you can try to create calculated field MetricASum as

              sum(metricA_dept01+metricA_dept02+metricA_dept03)

              and then divide it by max(metricB)

               

              if you have

               

               

              then I would do:

              Adept1:

              if [measure] ='metricA_dept01' then sum([value]) end

               

              ShareAdept1

              [Adept1] / sum(metricB_dept01)

               

              for metricb you need to choose whether sum(), Min(), max() would be the best at it depends on data.

              you can create proper field for adept2 and adept3, sum it and then divide by sum of bdept1,2 and 3 to have total shares.

              • 4. Re: Combining data from 2 columns, only for selected data.
                Kerry Evert

                Thanks Maciek,

                That won't work with this data.

                 

                I need to try to create a metricB_dept01, metricB_dept02 and metricB_dept03.

                 

                So I sort of need something like this:

                if [department] = '01'

                then sum those values to create measure metricB_dept01

                • 5. Re: Combining data from 2 columns, only for selected data.
                  Maciek La

                  ok and what values you expect in department_type for emergency admission type 1 and emergency admission other?

                  • 6. Re: Combining data from 2 columns, only for selected data.
                    Maciek La

                    so it's gonna be something like:

                     

                    calculated field newDeparmentType:

                    if isnull([department_type])=false and [metric_calculation] = "AE_Attendances" then [department_type]

                    elseif isnull([department_type])=true and [metric_calculation] = "Emergency_admissions_other" then "Type Other"

                    elseif isnull([department_type])=true and [metric_calculation] = "Emergency_admissions_type1" then "Type 1"

                    end