Question about MAX in combination with Date-function

[Anonymous]

Dear experts,

Probebly an easy on for you but I am pretty new with this and I haven't found answer here yet so I was hoping somebody could help me with this.

What I need is a count of ID's, from a curten day (in this case the day before the last day in the data) .

One of the sugesstions I found here was:

COUNT ({$<[Picklisten.PickdatumDag]={$(='"='&DATE(MAX([Picklisten.PickdatumDag]-1))&'"')}>} [Picklisten.Picklijstnummer])

But this returns the total Count of all ID's (as in " Count ([Picklisten.Picklijstnummer]) "

[Picklisten.PickdatumDag] = a field from SQL database with a timestamp

[Picklisten.Picklijstnummer] = a field from SQL database with Unique ID's

Thanks!

[Anonymous]

Nobody?

[Miguel_Angel_Baeyens]

Hello,

Try the following

COUNT ({$<[Picklisten.PickdatumDag]={'$(=DATE(MAX([Picklisten.PickdatumDag]-1)))'}>} [Picklisten.Picklijstnummer])

I've modified a bit your original syntax. I've enclosed as weel the date(max()) expression in simple quotes, it should work that way. Anyway, try first in a new text object that

=DATE(MAX([Picklisten.PickdatumDag]-1))

Returns as expected.

Regards.

[#4]

Hi,

In your formula, you are subtracting 1 from Max(Date).

QV doesn't understand meaning of this one. It also has to be in Date format.

Use following and make corresponding changes in your formula.

Date(Date(FromDate,'DD-MMM-YY')-DAY(2),'DD-MMM-YY')

If problem still persists, let me know. Share sample data if possible.

Happy Thoughts

[Anonymous]

Thank you Miguel A. Baeyens and ravi.sub8.

Now I understand it a litle more the fields returns a long number and not a data format and if I understand it correctly the DATE-function wil make it a useable date-field.

And I think I am almost there.

I tried what Miguel suggested :

COUNT ({$<[Picklisten.PickdatumDag]={'$(=DATE(MAX([Picklisten.PickdatumDag]-1))'}>} [Picklisten.Picklijstnummer])

But I think somthing is missing here. I think the first [Picklisten.PickdatumDag] should also be in a DATE-format, right?

So I tried the following:

COUNT( {$<'(=DATE([Picklisten.PickdatumDag])'={'$(=DATE(MAX([Picklisten.PickdatumDag]-1))'}>} [Picklisten.Picklijstnummer])

But this just returns "No Data To Display"

I must be doing something wrong with the $ < ' > "( ] or } right?

[Miguel_Angel_Baeyens]

Hello,

Left and right parts of PickdatumDag should be in the same format. Usually, left part of equal in set analysis doesn't accept functions, just field names, so something like

COUNT( {$< [Picklisten.PickdatumDag] = {"=DATE([Picklisten.PickdatumDag]) = DATE(MAX([Picklisten.PickdatumDag]-1)))"}>} [Picklisten.Picklijstnummer])

or even

COUNT( {$< [Picklisten.PickdatumDag] = {"$(=MAX([Picklisten.PickdatumDag]-1))"}>} [Picklisten.Picklijstnummer])

Anyway, try a text object with

=MAX([Picklisten.PickdatumDag]-1)

and see if that returns a valid format comparable to [Picklisten.PickdatumDag]. If not, a date() conversion would be needed and I'd do that in the script rather than in the object.

Hope that helps.

[jonathandienst]

Hi

You are correct that [Picklisten.PickdatumDag] should be in date format.

Unfortunately, the LHS of the set analysis expression has to be a field, it cannot be an expression.

One option would be to create a field like [Picklisten.Pickdatum] in your load script which would be in date format.

Jonathan

[Anonymous]

Thanks again Miguel,

Max ([Picklisten.PickdatumDag]-1) does not return a date but a long number.

But (like you an Jonathan suggested) in my loading script I already edit this.
I used this in the loading script:

Pickdatum,
Date(Pickdatum) as PickdatumDag,

Am I doing something wrong here?

[Miguel_Angel_Baeyens]

That's correct. Now check that PickdatumDag is actually your date format, so you can add days by doing (in a new text object)

=Date(PickdatumDag + 1)

and that it displays properly. You may need to format your date input, depending on the format you are pulling from the database, for example

Date(Date#(Pickdatum, 'DD-MM-YYYY hh:mm:ss')) AS Pickdatum

If you are using a date like "21-09-2010 00:30:00"

[Anonymous]

Thanks again Miguel,

I think I now get how the DATE-function works .... almost

When I got to the table viewer and look at the content of the table (example) the field seems to have the right format ('DD-MM-YYYY hh:mm:ss') . Also when I use

=DATE([Picklisten.PickdatumDag]-1)

It returns the correct format ('DD-MM-YYYY hh:mm:ss')

So that seems to be solved.

But I still don't get the right resoled from the function so I gues I am doing something wrong there.
What I want is a count of the [Picklisten.Picklijstnummer] where the [Picklisten.PickdatumDag] is the same as the higest [Picklisten.PickdatumDag] minus 1, without making a selection.

When I use

COUNT( {$< [Picklisten.PickdatumDag] =

{"=DATE([Picklisten.PickdatumDag]) = DATE(MAX([Picklisten.PickdatumDag]-1))"}

>} [Picklisten.Picklijstnummer])

The result is 0. ??

I think there must be something wrong with the way I use the variable.
Because when I use

COUNT( {$< [Picklisten.PickdatumDag] =

{"=DATE(MAX([Picklisten.PickdatumDag]-1)) = DATE(MAX([Picklisten.PickdatumDag]-1))"}

>} [Picklisten.Picklijstnummer])

It does returns the total count of all [Picklisten.Picklijstnummer]. So the formule is not "wrong" It is just not right for what I am trying to do.