4 Replies Latest reply: Jan 3, 2011 7:51 AM by msadrikhcb RSS

    Expression as a Variable

      Dear All,

       

      I have a pivot table in which i have used the following expression,

      If (Match(Group,'OTH'),If([Position]='LONG',Sum(YTD_CV)))/ 1000

      So i get this result;

      error loading image

      I get the result i need in the red box.

      Now, however, I need to use the total - 12,958 as a variable so that it can be used in other tables.

      I tried using the following;

       

       

       

       

       





       

       

      Sum(Aggr(If(Match(Group,'OTH'),If([Position]='LONG',Sum(YTD_CV)))))/1000 but it displays a null value.





      Can someone help here?

      PS. I cannot post a sample since I open the file in server mode.

      King Regards,

      M.K.S

        • Expression as a Variable
          Muzammil Syed

          Hi,

          You can instead use

          if(Group='OTH' and Position='LONG',Sum(Total<Group> YTD_CV ))/1000

          It will give you the Sum(YTD_CV) aggregated on Group.

           



            • Expression as a Variable

              Hi Syed,

              Thanks for your reply, if i use the formula you suggested it still doesnt work, i think its because the Position in the expression is caluclated using other calculated expressions from the pivot table/. I cannot write the whole formula since it is too long.

              I just need to use the total of the column as a variable because i need to use it for calculation in another table/chart.

                • Expression as a Variable
                  John Witherspoon

                  I think you have two basic mistakes in your expression. First, correct, you can't use [Position] if it is just a named expression in a chart. You have to recalculate it in your new expression. For simplicity, I'm going to assume your expression is If(Rand()>.5,'LONG','SHORT'), which obviously it isn't. Substitute your own expression of course. Second, when you aggr(), you need to tell it what dimensions you want to aggregate by, in this case, Group and Currency. So I think you want something like this:

                  Sum(Aggr(If(Match(Group,'OTH'),If(If(Rand()>.5,'LONG','SHORT')='LONG',Sum(YTD_CV))),Group,Currency))/1000

                  Now, this particular example could be further simplified, but I wanted to directly use your existing expressions. Feel free to simplify from there.

                  If the expression for short or long is too big, consider making IT a variable, and using it in both places.