Set analysis on dates, using > operator

[mborsadw]

I have 2 tables A and B.

Table A has "Request Qty" field

Table B has 2 Date fields "ETA" and "Delivery Date".

There are some common fields between tables A and B.

I am loading tables A and B separately in my load script and letting Qlik do the join based on the common fields.

The following Set analysis expression does not filter at all.

Sum({<[ETA]={">$([Delivery Date])"}>} [Request Qty])

Similar expression using IF statement works fine.

SUM(If([ETA]>[Delivery Date], [Request Qty], 0))

What am I doing wrong?

I also tried the following variant:

Sum({<[ETA]={">$(=Date([Delivery Date]))"}>} [Request Qty])

Using this expression yields empty results.

I am using QS Desktop tool (3.1 SR1)

[maxgro]

if you a have a key field in Table 2 (if not you can create the key in the script, with autonumber)

Sum({<Key = {"=(ETA > [Delivery Date])" } >} [Request Qty])

[mborsadw]

How does this work?  ETA > Delivery Date will return -1 or 0 or 1 right?

How will it match an autonumber?

[sunny_talwar]

May be create a flag in the script:

Table2:

LOAD ETA,

          [Delivery Date],

          If(ETA > [Delivery Date], 1, 0) as Flag

FROM ....;

and then this use this expression

Sum({<Flag = {1}>} [Request Qty])

[jayanttibhe]

OR

You can avoid Set analysis by using like this :

Sum(Flag * [Request Qty])

Thanks

Jayant

[maxgro · Accepted solution]

"=...." is an expression search

if the Key is associated to one ETA and one Delivery date

every Kye value that meets te condition (ETA  >  delivery) will be returned

The Expression Search