2 Replies Latest reply on Dec 23, 2013 5:50 PM by Imran Akbar

    plotting a percentage of a calculated field

    Imran Akbar

      I'm working with a data set where we need to calculate lots of percentages, not absolute values.

      The problem is that the denominator is coming from a calculated field on a copy of the original data set (without any filters applied) - so I can't just use the built-in Analysis -> Percentage of trick.

       

      So I created a calculated field with this formula (all sales is a calculated field itself):

      ([subtotal sales]/[all sales])*100

      but when I plot it, the value is constant over time (even though I can see varying values in subtotal sales and all sales).  It's as if as soon as I do this, I lose all the date information that's part of the sales data, and just get a scalar value back.

       

      I've been struggling with this for 2 hours now, and could use some help.

       

      thanks,

      imran

        • 1. Re: plotting a percentage of a calculated field
          Tracy Rodgers

          Hi Imran,

           

          Are you able to post the packaged workbook so we can take a closer look?

           

          -Tracy

          • 2. Re: plotting a percentage of a calculated field
            Imran Akbar

            Hi Tracy,

               Unfortunately the data is proprietary, so I can't post my workbook; but I've attached a screenshot and the calculations I'm doing.  I managed to plot what I needed by doing a custom calculation that itself using 4 underlying custom calculations and the values of 2 parameters ('income' and 'retailer Parameter').  What I'd like to do now is plot that custom calculation, but for multiple parameter values - which doesn't seem to be possible using this method.

             

            Here are the 5 calculations I'm doing:

            All Sales = SUM([subtotal_implied])

            All Sales for Income Group = IIF([dem_income]=[income],[subtotal_implied],0)

            All Sales for Retailer = IIF([retailer]=[retailer Parameter],[subtotal_implied],0)

            All Sales for Retailer and Income Group = IIF([dem_income]=[income] AND [retailer]=[retailer Parameter],[subtotal_implied],0)

            Index = 100*(SUM([All Sales for Retailer and Income Group])/SUM([All Sales for Retailer ]))/(SUM([All Sales for Income Group])/[All Sales])

             

            In the attached screenshot, the Index is displayed in the top row, and is essentially the ratio of the next 2 rows, divided by the ratio of the bottom 2 rows (those rows look similar, but they've all got different scales on the y-axis).tableau.png

             

            Any suggestions on other ways to accomplish what I'm trying to do?

             

            thanks,

            imran