1 Reply Latest reply on Apr 6, 2016 5:47 PM by Esther Aller

    is it possible to combine figures calculated in separate worksheets in one calculation?

    Thomas Laughton Smith

      Hello all,

       

      Let me explain:

       

      In one worksheet I have months in the column tab and countd of members in the rows, giving me the total members each month.

       

      In another worksheet I have months in the column tab and countd of members in the rows, but I also have an LOD filter which looks at the number of jobs per month per member and excludes them if they've done more than one- hence I have the total number of members that have done only one job that month.

       

      What I would like, rather than total number of members that have done only one job that month, is this as a percentage total members i.e. the second lot of figures divided by the first lot of figures.

      The total number of members changes depending on what schemes are being filtered on so I can't just add a static number into the calculation.

       

      I know it's a really simple calculation manually by looking at the two tables and dividing one figure by the other but I have to show the actual percentage figure for a report and can't figure out how to get tableau to do it.

      I've tried formulas and am stuck.... is this even possible or will I have to revert to excel?

       

      Thanks in advance.

        • 1. Re: is it possible to combine figures calculated in separate worksheets in one calculation?
          Esther Aller

          Hey Thomas,

           

          Any time you need to use a number created in a worksheet using filters in another worksheet, you need to create a calculated field that incorporates the filter. For example you might have a calculation that incorporates the LOD filter to filter out members with less than one job per month that looks like:

           

          [Members w/ more than 1 job/month]:

          COUNTD(

          IF [# jobs per month per member] > 1

          THEN [Member Name]

          END

          )

           

          And then you would need to create the calculation to find the percentage of total members:

           

          [% of members w/ more than 1 job/month]:

          [Members w/ more than 1 job/month]

          /

          COUNTD( [Member Name] )

           

          I would need a twbx with the worksheets to help create the actual calculations needed, but this is hopefully enough to get you started!