topic Complex set analysis in chart expression in QlikView

Complex set analysis in chart expression

Fri, 03 Aug 2012 12:48:37 GMT

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?

Re: Complex set analysis in chart expression

Fri, 03 Aug 2012 14:22:40 GMT

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

Fri, 03 Aug 2012 14:29:04 GMT

Hi,

Thanks for your reply.

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

Re: Complex set analysis in chart expression

Fri, 03 Aug 2012 14:32:19 GMT

Could you post a sample of your qvw?

Re: Complex set analysis in chart expression

Fri, 03 Aug 2012 14:38:12 GMT

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