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]

Getting close I think ...

I edit my script to this:

Datum
Num(Datum) AS Datenum,

Which returns a number which I could use like this

Count({$< Datenum = {'$(=Num(Max(Datenum))-1)'}>} Picklisten.Picklijstnummer)

This should count al the [Picklisten.Picklijstnummer] maximum date number minus 1, right?

The only problem is that the field [Datum] not only contains the days but also the seconds and the minuts. So it doesn't return round numbers but it also returns 9 numbers behind comma (like:40125,267543987)

So what I need is a Num() -function in the loading script that only returns the 5 whole numers (in this case 40125) .

[Miguel_Angel_Baeyens]

Hello Dennis,

Use instead

Num(Date(Datum)) AS Datenum

since your date format has hours and minutes, and that's making those decimals.

Hope that helps.

[Anonymous]

Thanks Miguel,

But that still returns : 40448,297076157

I only want the (in this case) 40448.
I think I should add a format to the formula, maybe?

[Miguel_Angel_Baeyens]

You are right,

Num(Date(Datum, 'DD/MM/YYYY')) AS Datenum

might work. If it doesn't and still returns decimals, you can add

Round(Num(Date(Datum, 'DD/MM/YYYY'))) AS Datenum

Regards!

[Anonymous]

Thanks again Miquel,

When I use:

Num(Date(Datum, 'DD/MM/YYYY')) AS Datenum it still returns the decimals

When I use:

Round(Num(Date(Datum, 'DD/MM/YYYY'))) AS Datenum

It does returns the number without decimals, so I thought that was it....
But it rounds up, for example:

40449,638368634 returns 40450

And I need it to be 40449.

[Miguel_Angel_Baeyens · Accepted solution]

Hi Dennis,

Use Floor() instead of Round(). It should get the value as you want.

Regards.

[Anonymous]

Yes that is it!

Many thanks again Miguel!