7 Replies Latest reply: Feb 1, 2013 5:07 AM by Giampiero Cina RSS

    set analysis with sum(if()) in the script

      Dear all,


      I have a set analayis in a chart which is computing:


      if the AVG of the months 1 to 3 is bigger than 1.1*AVG of months 4 to 6, the AVG of months 1 to 3 is taken into consideration, otherwise the AVG of months 1 to 6.


      the set analysis looks like follow:


      if((Sum({$<BUCKET={">=-3 <=-1"}>} BucketQty)/3) > 1.01*(Sum({$<BUCKET={">=-6 <=-4"}>} BucketQty)/3),
                (Sum({$<BUCKET={">=-3 <=-1"}>} BucketQty)/3),
                          (Sum({$<BUCKET={">=-6 <=-1"}>} BucketQty)/6))


      Now,I am trying to do this in-script for different reasons.


      I tried the approach with LET, i.e.


      LET vTrend=if((Sum({$<BUCKET={">=-3 <=-1"}>} BucketQty)/3) > [...]



      I tried the approach of SUM(If(condition, value))

      but still not working


      Sum(if( BUCKET>=(-3) and BUCKET<=(-1)), BucketQty/3) > 1.01*(Sum(if(BUCKET>=(-6) and BUCKET<=(-4)), BucketQty/3)),
      Sum(if(BUCKET>=-3 and BUCKET <=-1), BucketQty/3),
      Sum(BUCKET>=-6 and BUCKET <=-1), BucketQty/6) as CalcTrend;


      Is it thus possible to achieve that set anallysis above mentioned, in the script?


      Many thanks