3 Replies Latest reply: May 18, 2013 10:50 PM by Lav Jain RSS

    Syntax - Sum

    Bradley Coyne

      Hi.

       

      Syntax errors, any ideas.

       

      trying to calculate sum on field Item Number.

       

       

      sum (([List Price (SEK)] * Qty) /([BaselistPrice(SEK)] * Qty) , [Item number]))

       

       

      Best

      Bradley

        • Re: Syntax - Sum
          Lav Jain

          if Item Number is not one of the Dimension in ur chart then

           

          sum(aggr(sum ([List Price (SEK)] /[BaselistPrice(SEK)] ), [Item number])))

           

          Note that u dnt need Qty in both the Numerator and Denominator as they will cancel out.

           

           

          If Item Number is a dimension, then you can simply do sum([List Price (SEK)] /[BaselistPrice(SEK)] )

            • Re: Syntax - Sum
              Bradley Coyne

              Thanks for the tip

              Hi i have attached a picture below of the problem i am facing, I can work out the first varians in the table.

               

              ([List Price (SEK)]  - [BaselistPrice(SEK)] / BaselistPrice(SEK)]

               

              however am looking an expression that can even work when Article number is not shown and you pivot down through other dimensions like Year varians and Customer (see below).

               

              Problem.jpg

               

              Best,

              Bradley

                • Re: Syntax - Sum
                  Lav Jain

                  hi,

                   

                  i see that u're getting the sums right except for the BaseList(SEK) due to null values.

                  There're quite a few methods to solve this.

                   

                  The best is to do it in script for the field [BaseList(SEK)]:

                   

                  if(isnull([BaseList(SEK)],0,[BaseList(SEK)]) as [BaseList(SEK)]

                   

                  try this and let me know if u get what u need