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

    Syntax - Sum

    Bradley Coyne



      Syntax errors, any ideas.


      trying to calculate sum on field Item Number.



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





        • 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).






                • Re: Syntax - Sum
                  Lav Jain



                  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