topic Re: Set analysis on dates, using > operator in QlikView

Set analysis on dates, using > operator

mborsadw — Fri, 04 Nov 2016 15:00:49 GMT

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 — Fri, 04 Nov 2016 20:02:26 GMT

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 — Fri, 04 Nov 2016 22:51:37 GMT

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

How will it match an autonumber?

sunny_talwar — Fri, 04 Nov 2016 23:14:07 GMT

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 — Sat, 05 Nov 2016 03:40:41 GMT

You can avoid Set analysis by using like this :

Sum(Flag * [Request Qty])

Thanks

Jayant

maxgro — Mon, 07 Nov 2016 10:30:34 GMT

"=...." 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