topic AW:Set analysis syntax problem with dates in QlikView

Set analysis syntax problem with dates

Tue, 20 Apr 2010 13:40:12 GMT

Hi,

In my 8.5 SR3 application, I've a date field used for selections only (that is to say an autogenerate field not linked to anything else) : DATE_MAX.

In my chart I'd like to write an expression which calculate a sum of sales where the date field DATE_OPERATION <= DATE_MAX selected.

It should be something like :

=Sum({$<DATE_OPERATION={"<=$(=max(DATE_MAX))"}>}SALES)

but it doesn't work (even in V9 version).

Do you see where is the error in my expression ?

Thanks for your help

Best regards

Set analysis syntax problem with dates

Tue, 20 Apr 2010 13:46:22 GMT

Is it possible that you're trying to expand an incorrect expression? Try this:

=Sum({$<DATE_OPERATION={"<=max($(DATE_MAX))"}>}SALES)

Set analysis syntax problem with dates

Miguel_Angel_Baeyens — Tue, 20 Apr 2010 15:14:50 GMT

Hello Bertrand,

Your expression seems fine to me. The only think I can think of is that your DATE_OPERATION and DATE_MAX fields date format differ, so you don't get any results. Is that possible? Does that expression work fine with an if() statement instead of the set analysis?

Regards.

Set analysis syntax problem with dates

Tue, 20 Apr 2010 16:38:00 GMT

I read it again; my previous suggestion doesn't have sense. I think that Miguel is in the correct way. Try using Date with the same format for both dates.

Set analysis syntax problem with dates

Tue, 20 Apr 2010 17:27:06 GMT

thanks for your answer,

I finally made it, but now I'm fighting to do the same type of formula but with an =

that is to say

DATE_1 = DATE_2 value :

sum({$<DATE_1={DATE_2}>} SALES)

Do you know how to write it correctly ?

Best regards

Set analysis syntax problem with dates

johnw — Wed, 21 Apr 2010 00:18:56 GMT

OK, so DATE_1 and DATE_2 have no connections, direct or indirect. You want to select a set of values in DATE_2, and then have the system behave like you instead selected those values in DATE_1? I think this:

sum({<DATE_1=DATE_2>} SALES)

AW:Set analysis syntax problem with dates

ManuelRühl — Wed, 21 Apr 2010 10:05:46 GMT

I have got the same problem, but how can I egalize the date formats ?

I have got a delivery Date, which is simply a number with a length of 7 or 8 chars (f.e. 1102010=01.10.2010 , 10102010=10.10.2010),

so I convert it:

(LEN(DELIVERY_DATE)=7,DATE(DATE#('0'&DELIVERY_DATE,'DDMMYYYY')),DATE(DATE#(DELIVERY_DATE,'DDMMYYYY'))) AS DELIVERY_DATE,

And I have got a BESTBEFORE_DATE:

DATE(DATE#(BESTBEFORE_DATE,'DDMMYY')) AS BESTBEFORE_DATE,

Both dates appear in the same format .... but obviously they aren't.

My set analysis works fine with:

=SUM({$<DELIVERY_DATE={">$(=TODAY(0))"}>}AMOUNT)

AND THE IF-EXPRESSION IS WORKING, TOO

=SUM(IF(DELIVERY_DATE<BESTBEFORE_DATE,AMOUNT))

Set analysis syntax problem with dates

Wed, 21 Apr 2010 17:56:13 GMT

Thanks all for your suggestion,

for my first question, I put the following expression in a variable :

V_DATE : DATE_OPERATION={'<=$(=max([DATE_MAX]))'}

and then in my expression I wrote :

Sum({$<$(V_DATE)>} Sales)

and now it seems to works (I not really see the difference except using ' instead of " ??)

For my second question (Date with = ) I'm gonna write another post with a little example. because I think my case is a bit more complex.

Thanks for your help

Best regards