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 · Accepted solution]

So your field format does not match the set analysis field modifier format.

Try

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

INCIDENT_DATE={"$(=Date(MAX(INCIDENT_DATE),'YYYYMMDDhhmmss))"}>} IN_INCIDENT_NO )

and if you want to test against the date only, you should use a date field instead of a timestamp.

If you don't have one and you can't create one, try

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

INCIDENT_DATE={">=$(=daystart(MAX(INCIDENT_DATE)))"}>} IN_INCIDENT_NO )

[ChristofSchwarz]

To narrow down the problem, approach it in 2 steps:

1.) Verify that your set expression works by using a text constant for the date. Try it like

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

2) If that pulls the right data, try to caluculate exactly that text in a separate text box first.

I think it is      DATE(max(INCIDENT_DATE), 'YYYYMMDDhhmmss')  // Watch out the upper and lower characters in the formatting string !! MM is not mm !!

3) If this is also done, merge the two things: Move that formula from the textbox inbetween the $(=....) part of your set expression. Looking at your expression, this has another Error. Max() does not accept a formatting string. Try this, (the 2nd row is what you had tested before in the text box)

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

    DATE(max(INCIDENT_DATE), ''YYYYMMDDhhmmss')

)"}>} IN_INCIDENT_NO )

[#4]

Thanks swuehl,

it helped...thanks againnnn...

Regrds,

Hassan

[#5]

Have got the solution, thanks for your comments and advise...i have ahieved this by doing the following.

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

    daystart(Max(INCIDENT_DATE))

)"}>} IN_INCIDENT_NO )

Thanks

Hassan

[#6]

Hi Folks,

Is there a way to get the previous day count for the same...

Regards,

Hassan