Count of sales between two times

[#1]

Hi All

I want to count how many items were sold between the times 09:00:00 and 17:00:00.

I have been using the below expression but it keeps coming up 00:00:00

count( IF(DateTime2>='09:00:00'AND DateTime2<='17:00:00', salesNumber ))

I also need to be able to just see this expression for a weekend count, any ideas how i would add this in as i have a week day name field containing the days of the week e.g Sat, Sun

Many Thanks

Emma 

[swuehl]

Emma,

is you DateTime2 containing both Date and Time?

Then try this:

count( IF(time(DateTime2)>='09:00:00'AND time(DateTime2)<='17:00:00', salesNumber ))

And for your second request:

count( {<WeekDay = {5,6}>} IF(DateTime2>='09:00:00'AND DateTime2<='17:00:00', salesNumber ))

You could also convert your if() to a set expression, for this I would create an additional Time field in your data model, or do you already have one?

Regards,

Stefan

[#3]

The DateTime2 is just a Time field

Thanks

Emma

[swuehl]

And the format of field DateTime2 is 'hh:mm:ss' (and not eg. 'hh:mm TT')?

Sorry if I keep asking stupid questions...

Could you upload a small sample here to the forum?

[swuehl]

I just checked, if you created your DateTime2 field from a timestamp field just by using something like

LOAD

...

time(TIMESTAMPFIELD) as DateTime2,

...

from Table;

then you will get values formatted as time, but the numeric representation is still untouched, so probably around some 40000+.

You could check this by e.g. creating a list box for field DateTime2 and in list box properties, create an additional expression on tab expression:

=num(DateTime2)

If you compare such a DateTime2 field value with a literal, like

DateTime2 <= '17:00:00'

I believe the literal will be translated to a numerical value below 1 (its numerical representation), so you won't get a match for the DateTime2 value I assume you have.

Well, this won't explain that - as you said above - the result keeps coming as 00:00:00. I would assume a count to result in integer values, 0 in this case.

I this all seems to be an explaination, what can we do? Well, you could try:

=count( if(time(frac(DateTime2))>='09:00:00' and time(frac(DateTime2))<='17:00:00',DateTime2))

to only take the time part of your field values. If this helps in retrieving your correct count, I would suggest using something like time(frac(TIMESTAMPFIELD)) as DateTime2 in your load script.

If I am still totally off, please post some more information, best using a small sample app.

Have a nice weekend,

Stefan