1 Reply Latest reply: Oct 19, 2012 5:51 PM by Stefan Wühl RSS

    Problem charting Time functions

      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:

       

      Hora.png

       

      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
          Stefan Wühl

          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