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!
Hi Dennis,
Use Floor() instead of Round(). It should get the value as you want.
Regards.
Nobody?
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.
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
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?
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
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.=MAX([Picklisten.PickdatumDag]-1)
Hope that helps.
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
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?
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"
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.