Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum sales last hour

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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).

View solution in original post

1 Reply
swuehl
MVP
MVP

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).