topic Re: Unable to use Max(date) in a set expression in QlikView

Unable to use Max(date) in a set expression

Fri, 13 Sep 2013 11:09:23 GMT

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

Re: Unable to use Max(date) in a set expression

swuehl — Fri, 13 Sep 2013 11:14:34 GMT

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 )

Re: Unable to use Max(date) in a set expression

Fri, 13 Sep 2013 11:19:45 GMT

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

Re: Unable to use Max(date) in a set expression

Fri, 13 Sep 2013 11:25:54 GMT

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

Re: Unable to use Max(date) in a set expression

swuehl — Fri, 13 Sep 2013 11:30:16 GMT

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 )

Re: Unable to use Max(date) in a set expression

Fri, 13 Sep 2013 11:38:36 GMT

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

Re: Unable to use Max(date) in a set expression

ChristofSchwarz — Fri, 13 Sep 2013 11:48:44 GMT

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:

"=MAX ..." will not calculate anything but consider this a text constant, put "$(=MAX( ....))" instead
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
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

Re: Unable to use Max(date) in a set expression

Fri, 13 Sep 2013 12:00:38 GMT

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

Re: Unable to use Max(date) in a set expression

Fri, 13 Sep 2013 12:04:37 GMT

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

Re: Unable to use Max(date) in a set expression

Fri, 13 Sep 2013 12:09:43 GMT

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

Re: Unable to use Max(date) in a set expression

swuehl — Fri, 13 Sep 2013 12:11:10 GMT

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 )

Re: Unable to use Max(date) in a set expression

ChristofSchwarz — Fri, 13 Sep 2013 12:11:55 GMT

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 )

Re: Unable to use Max(date) in a set expression

Fri, 13 Sep 2013 12:58:13 GMT

Thanks swuehl,

it helped...thanks againnnn...

Regrds,

Hassan

Re: Unable to use Max(date) in a set expression

Fri, 13 Sep 2013 18:22:54 GMT

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

Re: Unable to use Max(date) in a set expression

Fri, 13 Sep 2013 18:25:31 GMT

Hi Folks,

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

Regards,

Hassan