Filter specific date dd.mm.rrrr eg. 22.10.2023

[Sebastian_Dec]

Hello, how to create a filter for a specific date in Qlik Nprinting (eg. 22.10.2023)?
I've probably tried every combination:

Value is =45217
Value is ="22.10.2023"
Value is =22.10.2023
Value is 22.10.2023
Evaluate value is Day(22.10.2023)
Evaluate value is Date(22.10.2023)

The value is Date, and takes specific dates:

In Data Load Editor:
date(hid_data) as Data,

I'm sure the value Data is correct beacuse this formula works:  Evaluate value = Day(Today()-1)

EDIT:
I try: Advanced search ='18.10.2023' and its works, but for a different reason, the report has errors and something is miscalculated. This error does not occur with Evaluate value = Day(Today()-1), but I want to provide a specific date and not count from today.

[Ruggero_Piccoli · Accepted solution]

Hi,

Please take the time needed to add the correct label of the version of the software you are using. 

Did you checked old community posts on the topic like:

• https://community.qlik.com/t5/Qlik-NPrinting/Nprinting-Date-Filter/td-p/78436
• https://community.qlik.com/t5/Qlik-NPrinting/Filter-based-on-date/td-p/1859048

Plus the reare the articles by @Lech_Miszkiewicz :

• https://nprintingadventures.com/2022/03/19/filters-in-nprinting-how-hard-can-it-be-part-3-evaluate-v...
• https://nprintingadventures.com/2019/02/15/the-pitfalls-of-nprinting-filters-part-1-dates-and-duals/

Best Regards,

Ruggero

[Lech_Miszkiewicz · Accepted solution]

@Sebastian_Dec 

Agree with @Ruggero_Piccoli - re-read all the articles I wrote about data types and filters.

You must understand your Qlik data first to be able to apply correct filter. 

By looking at your examples you are trying things which to me dont make sense, like: "Evaluate value is Date(22.10.2023)". That from the core of how "Date" function works is just wrong! Same with your attempt to use "Day" which will not make sense neither. 

You first question should be:

1. what is my date field data type in Qlik Sense
2. If it is Date/Integer/Numerical you need to provide Numerical value using either "numerical value is" option which would be hardcoded or "evaluate value" with function returning numerical representation of the date.

Understanding core basics of your data types is absolute bare minimum to then apply correct filters. 

now looking at your examples again:

Value is =45217 - this is wrong as likely your date is integer which requires "Numeric Value is option"
Value is ="22.10.2023"- this is wrong as likely your date is integer which requires "Numeric Value is option" and value you are providing is string
Value is =22.10.2023 - this is wrong as likely your date is integer which requires "Numeric Value is option"

Agree with @Ruggero_Piccoli - re-read all the articles I wrote about data types and filters.

You must understand your Qlik data first to be able to apply correct filter. 

By looking at your examples you are trying things which to me dont make sense, like: "Evaluate value is Date(22.10.2023)". That from the core of how "Date" function works is just wrong! Same with your attempt to use "Day" which will not make sense neither. 

You first question should be:

1. what is my date field data type in Qlik Sense
2. If it is Date/Integer/Numerical you need to provide Numerical value using either "numerical value is" option which would be hardcoded or "evaluate value" with function returning numerical representation of the date.

Understanding core basics of your data types is absolute bare minimum to then apply correct filters. 

now looking at your examples again:

Value is =45217 - this is wrong as likely your date is integer which requires "Numeric Value is option"
Value is ="22.10.2023"- this is wrong as likely your date is integer which requires "Numeric Value is option" and value you are providing is string

Value is 22.10.2023 - this is wrong as likely your date is integer which requires "Numeric Value is option"and value you are providing is string
Evaluate value is Day(22.10.2023) - this is wrong as Day function returns value from 1-31 and not date
Evaluate value is Date(22.10.2023) - this is wrong as Date() function applies format of the date and does not convert actual text value to a date. Instead you could use num(Date#('22.10.2023','DD.MM.YYYY')) which as documented would translate string to a date and as per Qlik Sense data type be converted to number.

TOpic is super easy but you must get the understanding of fundamentals.

cheers

[Lech_Miszkiewicz]

Hi @Sebastian_Dec 

Other things you dont mention and you dont take care in load script are possible timestamps on date field.

date(hid_data) as Data only apply default mask on date/time value. It can be 22.10.2023 at 12:00 am or maybe 22.10.2023 at 3:00 pm. You are not ensuring here that you only have date (and not time) by applying date(floor(hid_data)).

If you would apply date(floor(hid_data)) then in nprinting you would use:

• numerical value is: 45221
• or num(MakeDate(2023,10,22))

[Sebastian_Dec]

@Lech_Miszkiewicz @Ruggero_Piccoli thank you very much for so many valuable tips and guides.
I'll check everything and let you know by the end of the week.

I quickly tried the method with date(floor(hid_data)) and numerical value is: 45221 or num(MakeDate(2023,10,22)), but it still shows me errors, so I have to determine exactly what I'm doing wrong.

[Lech_Miszkiewicz · Accepted solution]

Hi @Sebastian_Dec

If you create field like I suggested then your filters should work:

• numerical value is: 45221
• evaluate value: num(MakeDate(2023,10,22))

If it still does not work then:

• it is possible you have other filters which cause issue
• you have unsupported objects (always one selected value) or triggers or other (check documentation)
• you have not regenerated metadata

good luck and let us know how you go

cheers.