Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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).
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).