Restrict hours in dimension Qliksense

[js96]

Hi,

Right now, I have used in Script, the following 

Time(round(frac(createdTimestamp),1/24/4) ,'hh:mm') as Hourly  - This is to get every 15 mins 

and in dimension, Hourly

I would like to restrict last 5 hours dynamically in dimension. Please help in achieving that:

Thanks in advance!

[anthonyj]

Hi,

You can dynamically subset your data to the last 5 hours in the measure with set analysis. Qlik holds its timestamps as decimals so 5 hours divided by 24 = 0.208333 of a day. This, you can pass through to the Now( ) function as a subtraction. Depending on whether you need the current time minus 5 hours or you want to base it on your last load time you'll need either Now( ) or Now(0)

I don't know what your measure you're using is and I'm assuming that Created Timestamp is a date and time, but it would look something like this:

count({$<[Created Timestamp]={">=$(=now(0)-.2083333)"}>}Column)

Let me know how it goes.

Regards

Anthony

[anthonyj]

Hi,

We can add a set analysis to your measure using the time#( ) function restricting the numbers which should also restrict your dimension. Depending on whether you need to restrict to the last 5 hours from now or your last load time you'll need to use either. Now(0) or Now( ). I usually Now(0) as it is more contextual to the data at the time of the last load but you can make a call on that.

I don't know what your measure is but the set analysis would look like this.

count({$<[Created Timestamp]={">=$(=now(0)-time#(5,'h'))"}>}Column)

Let me know how it goes.

Thanks

Anthony

[js96]

Hi Anthony,

It became an invalid dimension. Can you suggest using if condition: like I tried this, not sure what's wrong:

if(time(hour(timestamp#(Now())-MakeTime(5)),'hh:mm'),Hourly)

There was no impact.

Thanks!:)

[anthonyj]

Hi,

That post disappeared on me the first time so I re-wrote it and now it's come up twice. Weird.

I believe Qlik prefers to be subset in the measure using set analysis but if you want to put it in an 'if' condition then we may need to take some things into account.

I like the "Hourly" column you've created , I haven't seen that before.

"Hourly" is now only the time part of the date time column which means that if you have previous dates in your data and you only want to keep the last 5 hours your "if" condition will need to test off the original column comparing with the current date time.

if([Created Timestamp] > (now()-time#(5,'h')), Hourly)

Give that a try.

Thanks

Anthony