Sum sales last hour

Fri, 01 Jun 2012 09:58:36 GMT

Hi,

I'm struggling to use set analysis to sum sales over the last hour. I'm currently using the following expression:

=sum({<transdate = {'>$(=date(today(0)))'}, transhour = {'>$(=time(now(0)-(1/24)))'} >} sales)

Instead of summing sales last hour, it's summing today's sales. Once I get this to work, my goal is to create a sales alert to notify me if the last hours sales are below a certain threshold.

Any ideas where I'm going wrong? I've attached an example below. The example is in the text box on the left.

Best,

Matt

Re: Sum sales last hour

swuehl — Fri, 01 Jun 2012 10:38:36 GMT

Matt,

your transdate are not recognized as timestamp data type, adjust your standard format to

SET TimestampFormat='M/D/YYYY h:mm';

or use timestamp#() function with an appropriate format.

Then, you are using hour(transdate) for transhour, which is not a time format.

You could use hour(), but then you need to use hour() also in your search expression in the set modifier:

=sum({<transdate = {'>$(=date(today(0)))'}, transhour = {'>=$(=hour(now(0)-(1/24)))'} >} transactioncount)

Hope this helps,

Stefan

edit: and since your transdate is a timestamp, you could also just use:

=sum({<transdate = {">=$(=(now(0)-(1/24)))"} >} transactioncount)

This will also handle your day changes correctly (I believe the first version will not handle the first hour of a day correctly).

topic Re: Sum sales last hour in QlikView

Sum sales last hour

Re: Sum sales last hour