Solved: Re: Set analysis + variable as date and now() - Qlik Community

Hakim-A · ‎2018-04-27

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

sunny_talwar · ‎2018-04-27

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)

sunny_talwar · ‎2018-04-27

Try this

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

olivierrobin · ‎2018-04-27

hello

are you sure

start_time-($(=vhour))*0.041

returns a valid timestamp ?

Hakim-A · ‎2018-04-27

Yes I am sure

sunny_talwar · ‎2018-04-27

Did you try this?

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

Hakim-A · ‎2018-04-27

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 · ‎2018-04-27

Attach a sample where you can test

sunny_talwar · ‎2018-04-27

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 · ‎2018-04-27

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)

Hakim-A · ‎2018-04-27

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