topic Re: Min(date) confusing.. in QlikView

Min(date) confusing..

Fri, 04 Dec 2015 20:11:04 GMT

I am trying to find the min "call time stamp" (CTS) for each user. But min(time stamp) doesn't seem to work as either an expression or a dimension (I figured the latter wouldn't, so I tried it again as "aggr(min(CTS),USER)"; that didn't work either.

Is there some special way to handle dates in the min and max functions? (I need to find max as well).

I'd actually like to do this in my LOAD script. Currently, it is:

OutSales:

LOAD

Call time as CTS,

caller id as USER,

calllength as length

FROM (excelfile)

What am I missing here? thanks!

Re: Min(date) confusing..

sunny_talwar — Fri, 04 Dec 2015 20:13:53 GMT

What if you try this:

=Aggr(Min(TOTAL OutSales.CTS), USER, OutSales.CTS)

Re: Min(date) confusing..

Fri, 04 Dec 2015 20:31:44 GMT

Sorry, still comes back as NULL..

I'm sure this has something to do with full time stamps, because aggr(min(date), something else) has worked for me in the past. But I need the hh:mm:ss part because I want to find the first and last call made each day.

I wondering if I should just add something like

LOAD

frac(CTS) as hourstamp

find min(hourstamp)

and then just use date(hourstamp,'hh:mm:ss') when I display it?

Re: Min(date) confusing..

sunny_talwar — Fri, 04 Dec 2015 20:42:18 GMT

Can you share a sample to look at your issue?

Re: Min(date) confusing..

Fri, 04 Dec 2015 20:43:21 GMT

This is bizarre.. I added the line

frac(CTS) as hourstamp

to my script and reloaded. Now the table looks like this:

and I didn't change another thing. I seem to recall that date-times imported directly from excel have some issues, but can't remember the details.

Re: Min(date) confusing..

sunny_talwar — Fri, 04 Dec 2015 20:45:48 GMT

Hahahaha cool, it seems that its working now, you just need Time() or TimeStamp() function around your expressions now