1 Reply Latest reply: Aug 21, 2018 10:32 AM by Felipe Andrade RSS

    Calculation Help

    Mahitha M

      Hi Experts,

       

      Can any one please help me on below requirement.

      I have a table like below

       

      Product ID                 Product Name       Sale1                          Sale2               Sale1%             Sale2%

      18694                       Mobiles                    0                                 6145896          100%                100%

      rthuk9                       Clothes                   5,622,434                    231,337           85%                   25%

      rthuk9                       Clothes                   979,894                       700,688           15%                   75%

      58632                       Electronics             26,330884                    8,248,198         1%                     0%



      Sale1% is calculated as

      Column(1)/

      Sum(TOTAL <Product ID>)

      Aggr(RangeMax(0,Sum(Value))+RangeMax(Sum(Item1)+Sum(Item2))/5+0.1,Date,[Product ID], [Product Name]))


      Sale2% is calculated as

      Column(2)/

      Sum(TOTAL <Product ID>)

      Aggr(-RangeMin(0,Sum(Value))+RangeMax(Sum(Item1)+Sum(Item2))/5+0.1,Date,[Product ID], [Product Name]))


      The requirement is need to show only Product IDs where Percentage is not equal to 0% and 100%


      Please help me on this.


      Thanks in advance.


        • Re: Calculation Help
          Felipe Andrade

          Make an if statement!

          Is a bit odd at the beginning bit it works. It goes like this:

          Assuming Sale1% as a real name field and so on,

           

          SALE1%->

           

          if(  [Sale1%] <> 1 and [Sale1%] <> 0 ,

          //CODE FOR TRUE

          Column(1)/

          Sum(TOTAL <Product ID>)

          Aggr(RangeMax(0,Sum(Value))+RangeMax(Sum(Item1)+Sum(Item2))/5+0.1,Date,[Product ID], [Product Name]))

          //NO CODE FOR FALSE

          )


          SALE2%->


          if(  [Sale2%] <> 1 and [Sale2%] <> 0 ,

          //CODE FOR TRUE

          Column(2)/

          Sum(TOTAL <Product ID>)

          Aggr(-RangeMin(0,Sum(Value))+RangeMax(Sum(Item1)+Sum(Item2))/5+0.1,Date,[Product ID], [Product Name]))

          //NO CODE FOR FALSE

          )


          The code for true is your code.


          Like, mark as helpful and mark as the answer if it is. That helps a lot.

          Thanks!