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: 
Hakim-A
Creator
Creator

Set analysis + variable as date and now()

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

1 Solution

Accepted Solutions
sunny_talwar

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)


Capture.PNG

View solution in original post

10 Replies
sunny_talwar

Try this

=Count({$<start_time={"$(= '>=' & (start_time-($(=vhour))*0.041) & '<=' &now())"}>} distinct transaction_id)

olivierrobin
Specialist III
Specialist III

hello

are you sure

start_time-($(=vhour))*0.041

returns a valid timestamp ?

Hakim-A
Creator
Creator
Author

Yes I am sure

sunny_talwar

Did you try this?

=Count({$<start_time={"$(= '>=' & (start_time-($(=vhour))*0.041) & '<=' &now())"}>} distinct transaction_id)

Hakim-A
Creator
Creator
Author

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

Hakim-A
Creator
Creator
Author

Attach a sample where you can test

  

start_timetransaction_id
27/04/18 13:07:11432377308
27/04/18 13:07:01432377229
27/04/18 13:06:52432377144
27/04/18 13:06:36432377027
27/04/18 13:06:25432376957
27/04/18 13:06:14432376887
27/04/18 13:05:53432376758
27/04/18 13:05:44432376712
27/04/18 13:05:35432376649
27/04/18 13:02:59432375732
27/04/18 13:02:49432375665
27/04/18 13:02:37432375570
27/04/18 12:57:18432373648
27/04/18 12:57:09432373597
27/04/18 12:57:00432373554
26/04/18 18:46:06432074087
26/04/18 18:45:56432074023
26/04/18 18:45:46432073974
26/04/18 18:39:54432071635
26/04/18 18:39:45432071576
26/04/18 18:39:36432071529
26/04/18 16:33:12432012194
26/04/18 16:33:03432012116
26/04/18 16:32:53432011997
26/04/18 13:29:09431899981
26/04/18 13:28:59431899903
26/04/18 13:28:50431899821
26/04/18 13:23:33431897122
26/04/18 13:23:24431897064
26/04/18 13:23:15431896991
26/04/18 13:21:42431896166
26/04/18 13:21:32431896113
26/04/18 13:21:23431896033
26/04/18 12:23:26431866864
26/04/18 12:23:17431866810
26/04/18 12:23:08431866724
26/04/18 12:22:02431866271
26/04/18 12:21:53431866225
26/04/18 12:21:43431866179
sunny_talwar

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)

sunny_talwar

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)


Capture.PNG

Hakim-A
Creator
Creator
Author

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