Nprinting filter on last 16hours

[christianflood]

Hello, I have a dataset with a field  formated as timestamp 'YYYY-MM-DD hh:mm:ss'

I would like to filter out everything from now and 16 hours ago, how would I do that?

The report is to be executed 8:00 AM, and need to hold data from 16:00 PM the day before, and not any data from earlier than 16:00.

When working with day filters it is pretty easy, first makedate of the timestamp and then filter =now(today()-1) but then I get all data from 00 previous day until now, which I dont want.

The reason is that the data Im looking at corresponds to a nightshift working 16:00->08:00.

Thanks for any help.

/Chris

[Lech_Miszkiewicz]

Hi Christian

What version of NPrinitng are you working with? It is quite important to tell us the version as everything what i wrote below is focused mainly on NPrinting 17 and newer versions.

if you are on version 16. the approach can be different.

Text format of the field is the one thing, the other thing is that NPrinting (especially NPrinitng 17 and newer) is more specific about data types (text, numeric, mixed (dual)).

My suggestion is to make sure that your timestamp is actually dual value where Timestamp format is as you described 'YYYY-MM-DD hh:mm:ss', but it also holds the numeric part of it which will be integer for date and decimal place for time

for example:

7/7/2018  4:00:00 PM is 43288.6666666667 where 43288 is your date and 0.6666666667 is your time

I am not sure what you are trying to achieve by doing this: When working with day filters it is pretty easy, first makedate of the timestamp and then filter =now(today()-1) but then I get all data from 00 previous day until now, which I dont want.

Now(Today()-1) does not make sense to me...

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/DateAndTimeFun...

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/DateAndTimeFun...

Since Today() function always returns todays date startin at 00:00:00 (midnight) which in my example would be   7/7/2018 --> 43288 all what you need to do is deduct from it 8 hours which is 1-0.6666666667 = 0.333333333333333

Down to tricky part:

NPrinting 17 and newer does not support filters like: greater than" > or < less than. In NPrinting 17+ filter needs to evaluate to single value. https://help.qlik.com/en-US/nprinting/June2018/Content/ReportsDevelopment/Static-dynamic-filters.htm

In such case i would use set analysis and use a formula in it to produce objects already filtered by the required timestamp: for example

Sum({<Timestamp={">$(=Today()-0.333333333333333)"}>}    WorkeHours)

This approach will always produce objects showing yesterdays data from 4:00 PM onwards....

Benefits:

-you dont need to worry about NPrinitng handling filters, datatypes (NPrinting filters requires numeric values for numbers and duals (duals like for example months, dates and timestamps)

-your report should be generated faster as filters don't have to be applied

Cons

- it becomes quite static report (as at

hope this helps

regards

Lech

[christianflood]

Hi Lech, thanks for your respons.

Im using the lates june2018 nprinting together with the same version of Qlik Sense

I see now I wrote wrong, my function should have been num(Today()-1) which would give me all results for yesterday.

I will look into your respons and see if I can figure it out.

Br

Christian

[christianflood]

Hi again Lech, I dont fully understand this:  Sum({<Timestamp={">$(=Today()-0.333333333333333)"}>}    WorkeHours)

Would that be inserted into the field as a function?

[Lech_Miszkiewicz · Accepted solution]

Hi Christian,

Well - since you have clarified what version of NPrinting you are using - you have two options to consider:

1. create a flag during QlikView reload in load script
   1. If(num(Timestamp)>=(num(Today()-0.333333333333333)),1,0) as Flag
   2. Then in NPrinitng you would use a filter on Flag field = 1 and this would filter all required records
2. Second option is to hardcode logic in all objects used for NPrinting reports using set analysis, so in my example i just assumed that you could measure number of worked hours since 4PM last day and chart function would be then as i mentioned: Sum({<Timestamp={">$(=Today()-0.333333333333333)"}>}    WorkedHours)

There is no way to apply filters like: Timestamp >= Today()-0.333333333333333 directly in Nprinting. You need to do modification to your data model in Qlik to be able to achieve what you are asking.

First option is more robust as you only need to apply flag once and all objects will calculate according to this selection, Second option could be little bit faster in terms of performance but requires a lot more development since set analysis would have to be applied on all objects/expressions used in report

regards

Lech

[christianflood]

Hey thanks alot Lech!

I did the same thing now and applied it in the load script, then it was alot easier 😃

Thx alot for your help you saved my day!