0 Replies Latest reply: Nov 14, 2016 9:53 AM by Nishanth Nagappa RSS

    Quarter Hour Trend Graph Qlik Sense

    Nishanth Nagappa

      Hi All,

       

      I've recently started learning Qlik for a new project delivery at my work place. So apologies in advance if my question comes off as silly or simple.

       

      There is a need for me to plot a "trend" graph, which displays the number of sales that has happened in a 15-minute time-split (for upto 24 hours).

      My app has already been designed to get only the last 24 hours data. There is a field by the name "Transaction Created DT" which tells when a particular transaction (sales) has happened (includes date & time (hh:mm:ss))

       

      What I would like is a line graph which shows how many of those sales have happened in a 15-minute slots on X-Axis.

       

      For Ex:

       

      Data:

      ItemTransaction Created DT
      A09-11-2016 11:00:00 AM
      B09-11-2016 11:05:58 AM
      C09-11-2016 11:16:00 AM
      D09-11-2016 11:36:54 AM
      E09-11-2016 11:36:40 PM
      F10-11-2016 10:03:00 AM
      G10-11-2016 10:58:00 AM
      H

      10-11-2016 11:00:00 AM

       

       

      Line Graph:

      Assuming the current time is around "10-11-2016 11:00:00 AM", my app would load all of the above data (from A to H). Now, on the graph, I would like the following:  (attached image)

      QuarterHourTrendGraph_QlikSense.png

       

      I've tried creating a "master calendar/time" table which will help me get those "15-minute split" time from the Current Time using the following script. (The script gives me the perfect data btw, starting from [Current Time - 24 hours] and 96 rows with each row incremented by 15 minutes and upto the [Current Time]).

      But when I try to plot that ("PlotTime" field) on the X-Axis/Dimension and add a measure of "Count(Item)", the overall count is taken and in the case of the example above it would plot a constant "8" (counting from A to H) in the line graph without splitting them into their time.

       

      Data Script: (Some variables are replaced by their logic)

       

      LOAD

      Date(Floor(PlotTime)) AS Date,

      PlotTime,

      Hour(PlotTime) AS Hour,

      Minute(PlotTime) AS Minute;

      LOAD

          Timestamp($([Current Time - 24 hours]) + (RecNo() - 1)/96) as PlotTime

      AUTOGENERATE 98 * $([1 day]);

       

      Can anyone help me out with this?

       

      Thank you so much.