topic Re: show last 24 hours only - Qlik sense in App Development

show last 24 hours only - Qlik sense

joeybird — Tue, 19 Sep 2017 10:28:55 GMT

looking to find how I can use set analysis to only show the last 24 hours of activity

dimension is OrderDateHour

example

01/01/2001 01:00:00

01/01/2001 02:00:00

01/01/2001 03:00:00

I have to this split into separate fields as OrderDate and OrderHour

I have found code that takes away the last 24 hours, but that's not what I am looking to do

any ideas please

Kind Regards

Re: show last 24 hours only - Qlik sense

sunny_talwar — Tue, 19 Sep 2017 11:34:58 GMT

May be create a flag in the script for last 24 hours? But last 24 hours from what? the max date and time or now()? But this now can vary

Re: show last 24 hours only - Qlik sense

joeybird — Tue, 19 Sep 2017 11:38:41 GMT

It would be from the max date and hour

so at moment max date and hour is 19/09/2017 13:00

so from 13:00 going backwards each hour for the last 24 hours only (this would be sorted then on the chart)

Please help

Re: show last 24 hours only - Qlik sense

sunny_talwar — Tue, 19 Sep 2017 11:45:19 GMT

May be like this

{<DateTimeField = {"$(='>=' & TimeStamp(Max(DateTimeField) - 1, 'DD/MM/YYYY hh:mm') & '<=' & TimeStamp(Max(DateTimeField), 'DD/MM/YYYY hh:mm'))"}>}

Re: show last 24 hours only - Qlik sense

joeybird — Tue, 19 Sep 2017 12:09:49 GMT

this seems to work, thanks Sunny , but on the latest hour so e.g. 13:00 today it seems to double the figure ...

its adding todays 19/09/2017 13:00 with yesterdays 18 /09/2017 13:00 figure

please help

Re: show last 24 hours only - Qlik sense

sunny_talwar — Tue, 19 Sep 2017 12:11:47 GMT

Not sure I understand what you mean?

Re: show last 24 hours only - Qlik sense

joeybird — Tue, 19 Sep 2017 12:21:45 GMT

the max hour today's 13:00:00 is being added to yesterday's 13:00 hour's figure

please help

Re: show last 24 hours only - Qlik sense

sunny_talwar — Tue, 19 Sep 2017 12:23:55 GMT

so if max is 13, then you want to get this range, right

>=18/09/2017 13:00<=19/09/2017 13:00?

Re: show last 24 hours only - Qlik sense

joeybird — Wed, 20 Sep 2017 10:28:01 GMT

yes sort off

the calculation you made is sort of working, but its adding todays and yesterdays 13:00 time values (only the max hour is added double)

{<DateTimeField = {"$(='>=' & TimeStamp(Max(DateTimeField) - 1, 'DD/MM/YYYY hh:mm') & '<=' & TimeStamp(Max(DateTimeField), 'DD/MM/YYYY hh:mm'))"}>}

If I take away the -1 from the code

{<DateTimeField = {"$(='>=' & TimeStamp(Max(DateTimeField), 'DD/MM/YYYY hh:mm') & '<=' & TimeStamp(Max(DateTimeField), 'DD/MM/YYYY hh:mm'))"}>}

I get the correct number for todays 13:00 hour figure but all 0 for the other hours

Please help

Re: show last 24 hours only - Qlik sense

sunny_talwar — Wed, 20 Sep 2017 11:15:32 GMT

yes sort off

What is sort off mean... if you don't need this

>=18/09/2017 13:00<=19/09/2017 13:00

then what exactly do you need?

Re: show last 24 hours only - Qlik sense

joeybird — Wed, 20 Sep 2017 11:38:14 GMT

yes

>=18/09/2017 13:00<=19/09/2017 13:00

The app loads every hour, so the time will change so the next load after 13:00, will be 14:00

Please help

Re: show last 24 hours only - Qlik sense

sunny_talwar — Wed, 20 Sep 2017 11:40:31 GMT

Yes, that is what it should be doing

Re: show last 24 hours only - Qlik sense

joeybird — Wed, 20 Sep 2017 11:53:40 GMT

it seems to be adding the previous days max hour so e.g what ever the max hour is 14:00...it will add yesterdays 14:00 figure too , only to that bar on the bar chart

please help

Re: show last 24 hours only - Qlik sense

sunny_talwar — Wed, 20 Sep 2017 11:55:16 GMT

Can you share screenshots to show what you have and what you are getting?

Re: show last 24 hours only - Qlik sense

joeybird — Wed, 20 Sep 2017 12:03:58 GMT

cant really but number goes as follows

app runs every hour but this example app ran at 12:00

63 25 45 45 89 78 74 47 65 62 65 55 51 62 65 45 66 74 66 72 56 45 55 103 Y Axis (Number of Orders)

13 14 15 16 17 18 19 20 21 22 23 0 1 2 3 4 5 6 7 8 9 10 11 12 x Axis (Hour of the day)

the issue is on the 12 hour, as you can see its doubled the number (the figure is todays number for 12:00 and yesterdays 12:00)

please help

Re: show last 24 hours only - Qlik sense

sunny_talwar — Wed, 20 Sep 2017 12:17:34 GMT

May be a data model issue... not really sure what to tell you without seeing anything

Re: show last 24 hours only - Qlik sense

pradosh_thakur — Wed, 20 Sep 2017 12:20:10 GMT

hi guys

i might be completely wrong but can you please not use' <=' and use '<' in the last may be this

it is including both date 13:00 hour as you are using >= and <= .

{<DateTimeField = {"$(='>=' & TimeStamp(Max(DateTimeField) - 1, 'DD/MM/YYYY hh:mm') & '<' & TimeStamp(Max(DateTimeField), 'DD/MM/YYYY hh:mm'))"}>}

regards

Pradosh

Re: show last 24 hours only - Qlik sense

joeybird — Wed, 20 Sep 2017 12:47:47 GMT

this is working more team ,

sonny's was adding yesterdays and todays max hour figure

but the max time at this moment is 13:00..its giving me yesterdays figure instead of todays figure

rest of the hours going backwards is correct

please help

Re: show last 24 hours only - Qlik sense

pradosh_thakur — Wed, 20 Sep 2017 12:57:05 GMT

try this

{<DateTimeField = {"$(='>' & TimeStamp(Max(DateTimeField) - 1, 'DD/MM/YYYY hh:mm') & '<=' & TimeStamp(Max(DateTimeField), 'DD/MM/YYYY hh:mm'))"}>}

Re: show last 24 hours only - Qlik sense

joeybird — Wed, 20 Sep 2017 13:02:52 GMT

think we have it team

the answer is

DateTimeField = {"$(='>' & TimeStamp(Min(DateTimeField), 'DD/MM/YYYY hh:mm:ss') & '<=' & TimeStamp(Max(DateTimeField), 'DD/MM/YYYY hh:mm:ss'))"}

thanks team your awesome , bit of each Sunny and Pradosh code got it working

Kind Regards