Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am trying to count the number of transaction I have in the last x hours.
Table is
Transaction_id start_time
format start_time : 'DD/MM/YYYY hh:mm:ss'
I have a parameter called vhour that can go from 1 to 168.
I've written this formula
=count({$<start_time={"$(= '>=' & start_time-($(=vhour))*0.041 & '<=' &now())"}>} distinct transaction_id)
but it seems the first condition '>=$' & start_time-($(=vhour))*0.041 is not working
when I hard-code it like this
=count({$<start_time={"$(= '>=' & '27/04/2018 12:30:00' & '<=' &now())"}>} distinct transaction_id)
it works
Could you please help me fixing the 1st formula ?
Thank you
If I add 168 for vhour, I am getting 24 based on the above sample and this expression
=Count({$<start_time={"$(='>=' & TimeStamp(Max(start_time)-($(=vhour))*0.041, 'DD/MM/YYYY hh:mm:ss') & '<=' & Now())"}>} distinct transaction_id)
Try this
=Count({$<start_time={"$(= '>=' & (start_time-($(=vhour))*0.041) & '<=' &now())"}>} distinct transaction_id)
hello
are you sure
start_time-($(=vhour))*0.041
returns a valid timestamp ?
Yes I am sure
Did you try this?
=Count({$<start_time={"$(= '>=' & (start_time-($(=vhour))*0.041) & '<=' &now())"}>} distinct transaction_id)
Hi,
No I still have a total of 39 when using the data sample attached
I should have 15 as a result (if I consider the transaction_id between 12h52 and now)
Thank you for the support
Attach a sample where you can test
start_time | transaction_id |
27/04/18 13:07:11 | 432377308 |
27/04/18 13:07:01 | 432377229 |
27/04/18 13:06:52 | 432377144 |
27/04/18 13:06:36 | 432377027 |
27/04/18 13:06:25 | 432376957 |
27/04/18 13:06:14 | 432376887 |
27/04/18 13:05:53 | 432376758 |
27/04/18 13:05:44 | 432376712 |
27/04/18 13:05:35 | 432376649 |
27/04/18 13:02:59 | 432375732 |
27/04/18 13:02:49 | 432375665 |
27/04/18 13:02:37 | 432375570 |
27/04/18 12:57:18 | 432373648 |
27/04/18 12:57:09 | 432373597 |
27/04/18 12:57:00 | 432373554 |
26/04/18 18:46:06 | 432074087 |
26/04/18 18:45:56 | 432074023 |
26/04/18 18:45:46 | 432073974 |
26/04/18 18:39:54 | 432071635 |
26/04/18 18:39:45 | 432071576 |
26/04/18 18:39:36 | 432071529 |
26/04/18 16:33:12 | 432012194 |
26/04/18 16:33:03 | 432012116 |
26/04/18 16:32:53 | 432011997 |
26/04/18 13:29:09 | 431899981 |
26/04/18 13:28:59 | 431899903 |
26/04/18 13:28:50 | 431899821 |
26/04/18 13:23:33 | 431897122 |
26/04/18 13:23:24 | 431897064 |
26/04/18 13:23:15 | 431896991 |
26/04/18 13:21:42 | 431896166 |
26/04/18 13:21:32 | 431896113 |
26/04/18 13:21:23 | 431896033 |
26/04/18 12:23:26 | 431866864 |
26/04/18 12:23:17 | 431866810 |
26/04/18 12:23:08 | 431866724 |
26/04/18 12:22:02 | 431866271 |
26/04/18 12:21:53 | 431866225 |
26/04/18 12:21:43 | 431866179 |
May be the format need to be fixed
=Count({$<start_time={"$(= '>=' & TimeStamp(start_time-($(=vhour))*0.041, 'DD/MM/YYYY hh:mm:ss') & '<=' &now())"}>} distinct transaction_id)
If I add 168 for vhour, I am getting 24 based on the above sample and this expression
=Count({$<start_time={"$(='>=' & TimeStamp(Max(start_time)-($(=vhour))*0.041, 'DD/MM/YYYY hh:mm:ss') & '<=' & Now())"}>} distinct transaction_id)
Still not working
I don't understand why as when I create a straight table with dimension transaction id and
expression 1
=timestamp(start_time,'DD/MM/YYYY hh:mm:ss')
expression2
=TimeStamp(start_time-($(=vhour))*0.041, 'DD/MM/YYYY hh:mm:ss')
I can see it is calculating the good way
But in the set analysis not