Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Need some help with a complicated set analysis expression in a chart.
My chart is a straight table with the following:
Location | Date 1 | Date 2 | D |
---|---|---|---|
ABC | 24th June 2012 | 26th July 2012 | 120 |
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?
He,
Have you tried:
if( CM_Hyporun.DELIVERYDATE>=column(1) and CM_Hyporun.DELIVERYDATE<=column(2),sum(CM_Hyporun.STOCKUNITDELIVERYQTY)
gr.
Frank
Hi,
Thanks for your reply.
It doesn't seem to work. The value ' - ' is returned in the cell.
Could you post a sample of your qvw?
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