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

# 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?

• ###### Re: Complex set analysis in chart expression

He,

Have you tried:

if( CM_Hyporun.DELIVERYDATE>=column(1) and CM_Hyporun.DELIVERYDATE<=column(2),sum(CM_Hyporun.STOCKUNITDELIVERYQTY)

gr.

Frank

• ###### Re: Complex set analysis in chart expression

Hi,

It doesn't seem to work. The value ' - ' is returned in the cell.

• ###### Re: Complex set analysis in chart expression

Could you post a sample of your qvw?

• ###### Re: Complex set analysis in chart expression

QVW uploaded. Its the whole thing as I didn't know what I could take out without it breaking the functionality.

Table is on the 2nd tab, and its columns 1, 3 and 5 that the above query relates to.

Regards

Dan