Set analysis syntax problem with dates

[#1]

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

[#2]

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

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

[Miguel_Angel_Baeyens]

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.

[#4]

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.

[#5]

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

[johnw]

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)

[mruehl]

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:

IF

(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))

[#8 · Accepted solution]

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