Problem charting Time functions

Fri, 19 Oct 2012 21:23:52 GMT

Hello, Qlikview gurus.

I've been stuck for a couple days on a single chart i'm doing for a proyect. The drill is: Every single day, there are route requests, for the daily job. Now, for example, the June 6th, there were 3 requests. Every request has a settlement date, which may span several days after the request date (Jun 6th).

Eg.

The request No. 1 has a settlement date of June 6th 2012, 13:01:42

The request No. 2 has a settlement date of June 6th 2012, 18:39:33

The request No. 2 has a settlement date of June 7th 2012, 04:22:01

The chart need to show the Max settlement HOUR, according to the Max date, but ignoring it. So, the bar on the Jun 6th dimension should show 04:26:01, because 18 is bigger than 13, but the 7th one is technically bigger than 13. I already did a Group By on script, so i have the Max Date according the Request and Settlement date. The problem is: When i try to put the number on the chart, it does this:

As you can see, the resulting number is, in fact, the Max existing Settlement date (Spanning 'till 1 day after), but the chart doesn't move. Apparently, the charted number is 2012, the current year. Here´s my table script, maybe the issue is script related, or even you can give me an advice about using time-related functions:

OPORTUNIDAD_NARANJA:
LOAD 
     Date(SolicitudFechaProceso) AS %Fecha,
     UnidadOperativaCve AS %ID_UnidadOperativa,
     Time(MAX(TimeStamp(SolicitudFechaContabilizacion))) AS MaxHoraConta,
     MAX(Timestamp(SolicitudFechaDigitalizacion)) AS MaxFechaDigitalizacion,
     Max(TimeStamp(SolicitudFechaInicioLiquidacion)) AS MaxFechaInicioLiq
FROM
QVD\LRR_Solicitud.QVD
(qvd) group by SolicitudFechaProceso,
UnidadOperativaCve;

The table is Qualified, so no problem about all of those '%' and UnidadOperativaCve is just an identifier. MaxHoraConta is my time number, so maybe the function nesting is wrong, or something related.

Any solution/advice you can give to me?

Re: Problem charting Time functions

swuehl — Fri, 19 Oct 2012 21:51:29 GMT

Time(MAX(TimeStamp(SolicitudFechaContabilizacion))) AS MaxHoraConta

will just format the value as Time, but will not change the underlying numerical value (i.e. still contain the date part).

It seems that you format your bar chart on number tab as time, but this seems to display the year of the value for large numerical numbers (don't ask me why).

Try

Time(frac(MAX(SolicitudFechaContabilizacion))) AS MaxHoraConta

or if you only want the Hour

hour(MAX(SolicitudFechaContabilizacion)) AS MaxHoraConta

topic Problem charting Time functions in QlikView

Problem charting Time functions

Re: Problem charting Time functions