5 Replies Latest reply on Dec 23, 2013 8:03 AM by Elizabeth Se

    PVMC Analysis

    Elizabeth Se
    Elizabeth Se Dec 20, 2013 7:46 AM

      (ZN(TOTAL(SUM([Shipped Qty]))) - LOOKUP(ZN(TOTAL(SUM([Shipped Qty]))), -1))*(LOOKUP(ZN([Margin $]), -1))*(LOOKUP(ZN([Prod Mix %]), -1))

       

      Here is my current formula for volume, but the last part for product mix is throwing off the calculation.  Any ideas? 

      • 258 Views
      • Tags:
        • 1. Re: PVMC Analysis
          Aaron Clancy
          Aaron Clancy Dec 20, 2013 1:20 PM (in response to Elizabeth Se)

          Error message or incorrect results?

          • 2. Re: PVMC Analysis
            Elizabeth Se
            Elizabeth Se Dec 20, 2013 1:27 PM (in response to Aaron Clancy)

            Couple of issues.  The theory of the formula is correct but right now it

            returns $0 for volume, which is incorrect.

             

            The data is organized by item, product group, and then product category.

            The formula should be

             

            (total volume for current year - total volume for the prior year)  *

            (margin $ for the item for the prior year* product mix % for the prior year

             

            I can't get the formula to reference the grand totals for the first part of

            the calculation or reference the item level data for margin$ or mix %.

             

             

            On Fri, Dec 20, 2013 at 4:20 PM, Aaron Clancy <

            • 3. Re: PVMC Analysis
              Murali Govindu
              Murali Govindu Dec 20, 2013 1:35 PM (in response to Elizabeth Se)

              Hi Elizabeth,

               

              I browse several times the forums to educate myself, and minimal times I will be able to answer but for sure will attempt to resolve.  It would be nicer if you could attach a packaged workbook for everybody here (you may mock in case what you have is a sensitive data for sharing) so that you may get an opinion which could turn out to be an answer for you.

               

              Thanks,

              murali.govindu@gmail.com

              • 4. Re: PVMC Analysis
                Andrew Ball
                Andrew Ball Dec 23, 2013 1:53 AM (in response to Elizabeth Se)

                One of the problems you will be having is that the "LOOKUP" function is a table calculation, so needs to work on aggregated fields.

                Both the [Margin $] and [Prod Mix %] fields need to be aggregated in some form in order for the calculated field to work. I think in your case, you can aggregate using MIN, MAX or ATTR. If all the records have the same value, these will leave the value unchanged, but allow the table calculation to work.

                • 5. Re: PVMC Analysis
                  Elizabeth Se
                  Elizabeth Se Dec 23, 2013 8:03 AM (in response to Andrew Ball)

                  Thanks!  The fields are already aggregated though and I need the actual

                  calculation to return instead of min or max.  The mix % varies for each

                  product.  How do I lock references in Tableau formulas like you can in

                  Excel?

                   

                   

                  On Mon, Dec 23, 2013 at 4:53 AM, Andrew Ball <