4 Replies Latest reply: Aug 3, 2012 10:38 AM by Dan Haire RSS

    Complex set analysis in chart expression

      Hi all,

       

      Need some help with a complicated set analysis expression in a chart.

       

      My chart is a straight table with the following:

       

       

      LocationDate 1Date 2D
      ABC24th June 201226th July 2012120

       

      The calculation of Date 1 and Date 2 are set analysis expressions.

       

      I then need to use the value within the cell for each row in the set analysis expression to calculate column D.

       

       

      Date 1 expression:

       

      FirstSortedValue

      ({<CM_GroupedbyparentB.MONTH=,CM_GroupedbyparentB.YEAR=>} CM_GroupedbyparentA.MEASUREDATE,-CM_GroupedbyparentA.MEASUREDATE)

       

       

      Date 2 expression:

       

      FirstSortedValue ({<CM_GroupedbyparentA.MONTH=,CM_GroupedbyparentA.YEAR=>} CM_GroupedbyparentB.MEASUREDATE,-CM_GroupedbyparentB.MEASUREDATE)

       

       

      Column D expression:

       

      sum ({< CM_Hyporun.DELIVERYDATE = {"<="}*{">="} >} CM_Hyporun.STOCKUNITDELIVERYQTY)

       

       

       

      Now here's the problem. Where I have the set analysis just in the middle of the column D expression, I need a less than or equal to the value in cell of Date 2 Column AND a greater than or equal to the value of the cell in Date 1 column. So the result of this should be a set analysis summing values between the two date values.

       

      I have tried using {"<=Column(1)"}*{">=Column(2)"} to pick up the values but this won't work.

       

      I have also tried repeating the full expressions for Date 1 and Date 2 again inside the set analysis expression for column D. Again this won't work.

       

       

      Any help?