3 Replies Latest reply on Nov 23, 2016 1:56 AM by Gavin Attard

    calculations with different cells.

    Gavin Attard

      Hi Guys

       

      what i am probably asking for can't be done, however i thought i'd put out there to see if anyone has managed.

       

      So i have a report that summs metrics against a couple of dimensions.

       

      see attached pic.

       

      What i want to achieve is the ability to multiply the values in the cells marked in yellow.

       

      Any ideas?

       

      tableau case2.JPG

        • 1. Re: calculations with different cells.
          Andrew Watson

          This will be possible although looks like it could be complicated as you have a table calculation involved.

           

          I can give you a guide on how to do. You need to extract the values in yellow within calculated fields, for example for the CG% it could be:

           

          IF [FieldName] = 'CG' THEN [% Value] END

           

          In reality I expect you will have to go deeper than that, back to the source of the % formula but using the same technique. This will return NULL for non CG values.

           

          The same is true for the NCG total - IF [FieldName] = 'NCG' THEN [TotalField] END.

           

          With the NCG total I also expect you will have to go deeper to pull out the NCG numbers.

           

          Once you have the required CG and NCG numbers you're able to create a new field to multiply these.

           

          For example:

           

          IF [FieldName] = 'NCG' THEN [TotalField] END * IF [FieldName] = 'CG' THEN [% Value] END

          • 2. Re: calculations with different cells.
            sudhakar reddy

            Hello Gavin,

             

            i've just made an attempt but the one which your trying to implement is not possible i guess or might be done using LOD expressions. But just created a workbook what you requested for

             

             

             

            Regards,

            Sudhakar Reddy

            • 3. Re: calculations with different cells.
              Gavin Attard

              Hi Andrew

               

              thanks for your help. Indeed that was the route i went down, and was able to pull the individual values. However the challenge i had is when i tried to multiply them. The issue was as you pointed it keeps hte metrics agaisnt the dimension, and is not able to multiply the cells...

              however in writing this, i think i may have though of a solution. I wonder if i can set within the calculated fields pulling the the value an additional dimensional value to tie it with. ie. take value and set CG/NCG = ICG.

               

              then both metrics appear in ICG and are calculable...

               

              i'll revert a bit later with whether this worked.

               

              kr