3 Replies Latest reply: Dec 19, 2011 4:53 PM by sam.horton RSS

    Adding calculated fields to Pivot Table

      For the object Product Line Total, I would like to calculate the variance between the months for the value Extension for both "R" and "F", but am struggling to figure it out.  I have attached the file.  As always any assistance is greatly appreciated.

        • Adding calculated fields to Pivot Table

          Assuming you always want [Product Type] R and F, and the variance between them:

           

          1. Drop the dimension [Product Type]

           

          2. Create three expressions and be explicit using SET analysis to get what you need.

           

          Expression for R:

          =sum

          (

              {<

                  [Product Type] = {'R'}

              >}

          Extension

          )

           

          Expression for F:

          =sum

          (

              {<

                  [Product Type] = {'F'}

              >}

          Extension

          )

           

          And, the variance:

           

          =sum

          (

              {<

                  [Product Type] = {'R'}

              >}

          Extension

          )

          -

          sum

          (

              {<

                  [Product Type] = {'F'}

              >}

          Extension

          )

           

          ....

           

          That is just one way.......

           

          Hope it helps.

            • Adding calculated fields to Pivot Table

              Sam,

               

              Thanks for the feedback, but I would like to get the variance of "F" between the month 1 and month 2, and of "R" between month 1 and month 2 not the difference betweet F and R.

                • Adding calculated fields to Pivot Table

                  So, you want R vs. prior month, F vs. prior month?

                   

                  There are a few ways, but let's take a direct approach.

                   

                  Modify your load script such that you have, on every row, a [Prior Month]

                   

                  That is done via addmonths([Month],-1) AS [Prior Month]

                   

                  Then, to get the prior month value of F, in your expression, use something like this:

                   

                  =sum

                  (

                      {<

                          [Product Type] = {'F'},

                          Month = [Prior Month]

                      >}

                  Extension

                  )