How to report sales per hour from a time stamp?

[#1]

hi all,

just started using QlikView, my apologies if this is a basic question.

Have tried googling it and work it out myself with little success

Issue:

Would like to see a graph or bar chart showing number of sales of a product over time.

The query is for a single day only so after something showing how many sales per hour or 30 min block through the day.

What I have is a table showing the productID and "sales_date" which looks like this "1/03/2014 12:05:24 AM".

I right-click > Properties the table and on the Number tab set "sales_date" to data format "Time".

then it displays "12:05:24 AM" at least.

I also have on each row a column called "sales_qty" which is always 1, as it's recording a time stamp per sale.

When I try to do a graph it shows up with "1" as the Y axis whatever I do.

Is it possible to do something like

-group sales per hour or 30 min block to see number of sales per time block?

-other way to sum sales by hour or other time period?

I am using the free, personal edition so does not seem to be able to share files with others.

thanks

Bjorn

[#2]

Time(MakeTime(Hour(sales_date)))

seems to make hours of the sales_date at least.

Now how to sum sales_qty by this?

[jagan · Accepted solution]

Hi,

You need to get a new column in script for hour, check the script below

Data:

LOAD

*,

Hour(sales_date) AS Hour

FROM DataSource;

If you are using database then try like this below

Data:

LOAD

*,

Hour(sales_date) AS Hour;

SELECT

*

FROM TableName;

Now use Hour as Dimension in your chart.

Note: if you don't want to change the script then try like this

Chart Properties -> Dimension -> Click on Add Calculated Dimension-> give Hour(sales_date)

Hope this helps you.

Regards,

Jagan.

[JonnyPoole]

The above will give you hour as a dimension.

then I think your expression will be:   sum(Sales_qty)

Also with the hours as the dimension, it can be helpful to designate the x axis (hour) as a 'continuous' axis so that the axis is not skewed chronologically. The 'continuous' setting is on the axis tab of the chart properties.

[JonnyPoole]

also i think if you want to chart the time periods in 30 min intervals, you can add a field ( i call it demihour) into your load script next to the hour expression.

if (Minute(StartDate)  <=30 , hour(StartDate) & ':00' , hour(StartDate)  & ':30' ) as  Demihour,

Hour(sales_date) AS Hour

[#6]

Great, really appreciate the help.

Got it working by using calculated dimension Hour(sales_date) as suggested, awesome.

The only issue is that the x axis is screwy, it has hours in random order it seems, rather than following the clock.

Tried setting it to Continues on the Axis tab but then the sales graph looks like a scribbble