topic Re: Set analysis + variable as date and now() in QlikView

Set analysis + variable as date and now()

Hakim-A — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set analysis + variable as date and now()

sunny_talwar — Fri, 27 Apr 2018 11:48:40 GMT

Try this

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

Re: Set analysis + variable as date and now()

olivierrobin — Fri, 27 Apr 2018 11:48:54 GMT

hello

are you sure

start_time-($(=vhour))*0.041

returns a valid timestamp ?

Re: Set analysis + variable as date and now()

Hakim-A — Fri, 27 Apr 2018 11:50:17 GMT

Yes I am sure

Re: Set analysis + variable as date and now()

sunny_talwar — Fri, 27 Apr 2018 11:51:43 GMT

Did you try this?

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

Re: Set analysis + variable as date and now()

Hakim-A — Fri, 27 Apr 2018 11:53:20 GMT

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

Re: Set analysis + variable as date and now()

Hakim-A — Fri, 27 Apr 2018 11:54:08 GMT

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

Re: Set analysis + variable as date and now()

sunny_talwar — Fri, 27 Apr 2018 11:55:03 GMT

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)

Re: Set analysis + variable as date and now()

sunny_talwar — Fri, 27 Apr 2018 12:01:12 GMT

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)

Re: Set analysis + variable as date and now()

Hakim-A — Fri, 27 Apr 2018 12:01:22 GMT

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

Re: Set analysis + variable as date and now()

Hakim-A — Fri, 27 Apr 2018 12:11:54 GMT

This formula works.

Thank you