Unable to use Max(date) in a set expression

[#1]

Hi all,

Can Somebody what is wron in the below set expression, im unable to use the date in the set expression, i want t get the count based on the max date, but its not giving me the intended result..

COUNT({$<REGION={'EAST','WN-EAST'},
INCIDENT_DATE={"=MAX(MakeDate(Year(INCIDENT_DATE),Month(INCIDENT_DATE),Day(INCIDENT_DATE)))"}>}IN_INCIDENT_NO )

when tried in a text object im getting the correct date, but the same is not working when used in a straight table expression...

Regards,

Hassan

[swuehl]

try with a dollar sign expansion:

COUNT({$<REGION={'EAST','WN-EAST'},

INCIDENT_DATE={"$(=MAX(MakeDate(Year(INCIDENT_DATE),Month(INCIDENT_DATE),Day(INCIDENT_DATE))))"}>}IN_INCIDENT_NO )

[#3]

Hi,

Try this

COUNT({$<REGION={'EAST','WN-EAST'},

INCIDENT_DATE={"=$(=MAX(MakeDate(Year(INCIDENT_DATE),Month(INCIDENT_DATE),Day(INCIDENT_DATE))))"}>}IN_INCIDENT_NO )

If you are using $ then it take it as expression and then calculated. Hope it helps you

[#4]

Hi AmuthaBharati,

Tried but its actually ignoring the date and giving me the total count, please suggest if there is any other way of getting te max of date...

Thanks,

Hassan

[swuehl]

I think you can simplify your expression to something like

max(INCIDENT_DATE)

and you may need to take care of the format of field INCIDENT_DATE. Assuming YYYY-MM-DD, try

COUNT({$<REGION={'EAST','WN-EAST'},

INCIDENT_DATE={"$(=Date(MAX(INCIDENT_DATE),'YYYY-MM-DD))"}>} IN_INCIDENT_NO )

[#6]

Hi Swuehl,

seems there is some problem in the formatting not getting the value that is expected, but have tried in the text object which is giving me the max date using the below,

max

(date(INCIDENT_DATE_DISPLAY,'DD/MM/YYYY'))

but when used in the set expression it doenst seems to be working, any advise..

Regards,

Hassan

[ChristofSchwarz]

It is just wrong format of the date value. Inside a set expression, like it or not, the format must be exactly as in the field. A IF comparision is more tolerant, it would consider 41530='2013-09-13' as true. Unlike in set expression. So tell me your date or date/time format of the field INCIDENT_DATE, then I can tell you how the set expression should read.

COUNT({$<REGION={'EAST','WN-EAST'},

INCIDENT_DATE={"=MAX(MakeDate(Year(INCIDENT_DATE),Month(INCIDENT_DATE),Day(INCIDENT_DATE)))"}>}IN_INCIDENT_NO )

Your above expression has 3 issues already:

1. "=MAX ..." will not calculate anything but consider this a text constant, put "$(=MAX( ....))" instead
2. Max, as a resutl, will return a number value, irrespective from whether the field inside the Max(..) statement was a date. See my opening words why this is unfortunate here
3. You seem to have tried to make the result of MAX a date by putting a MAKEDATE inside. Even when Makedate would be a formatting function (which it is not), it would only have helped, when had put it outside of the MAX(...) function. However, MakeDate does not format a date, it ironically returns an integer number instead (!). Something like 41530. Use "Date()" instead, which is a formatting function, that has a DUAL datatype as result, where the visible text is the date according to your "DateFormat"" variable and the number is an integer like 41530. 

Try this and I think it will shoot

COUNT({$<REGION={'EAST','WN-EAST'}, INCIDENT_DATE={"$(=Date(MAX(INCIDENT_DATE)))"}>} IN_INCIDENT_NO )

all the best

[#8]

Hi Schwarz,

Thanks for the information, yes what you said is absolutely right, but even then im failing to get the date.

COUNT({$<REGION={'EAST','WN-EAST'}, INCIDENT_DATE={"$(=max(

IN_INCIDENT_NO

,'MM/DD/YYYY')))"}>} IN_INCIDENT_NO )

Pleae suggest if there can something else i can try so to get the required date and can move onnnn...

Thanks,

Hassan

[#9]

also adding to the above the INCIDENT_DATE

format is as mentioned below,

20130515193956

yyyymmddhhmmss

and im expecting the result to be in MM/DD/YYYY,

please advise...

[#10]

Hi,

'If possible store it in variables and then use

vDate=MakeDate(Year(INCIDENT_DATE),Month(INCIDENT_DATE),Day(INCIDENT_DATE)

vMaxDate=Max(vDate);

are select directly as

vMaxDate=Max(INCIDENT_DATE);

and use those variables in the expression