Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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) .
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.
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?
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!
Thanks again Miquel,
When I use:
Num(Date(Datum, 'DD/MM/YYYY')) AS Datenum it still returns the decimals
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.
Hi Dennis,
Use Floor() instead of Round(). It should get the value as you want.
Regards.
Yes that is it!
Many thanks again Miguel!