topic Re: problem with a date filter in qlik nprinting in Qlik NPrinting

problem with a date filter in qlik nprinting

P_qlik — Thu, 08 Apr 2021 08:25:59 GMT

Hi,

I have a numeric field like this for a date: 202101 , 202102, 202103, 202104.

I used this formula that works in qlik view to have the current month: =Max(Date(Date#([YYYYMM (Anno - Mese)], 'YYYYMM'), 'YYYYMM'))

I put this formula in qlik nprinting filter but it doesn't work, why?

How can I do?

Thank you

Re: problem with a date filter in qlik nprinting

Ruggero_Piccoli — Thu, 08 Apr 2021 08:36:01 GMT

Hi,

Did you selected "Evaluate Value" on the drop down menu? https://help.qlik.com/en-US/nprinting/February2021/Content/NPrinting/ReportsDevelopment/Static-dynamic-filters.htm#anchor-5

Best Regards,

Ruggero

Re: problem with a date filter in qlik nprinting

P_qlik — Thu, 08 Apr 2021 08:39:47 GMT

Hi,

@Ruggero_Piccoli yes but it doesn't work, I tried with evaluate value and advance research but they don't work

Re: problem with a date filter in qlik nprinting

Lech_Miszkiewicz — Thu, 08 Apr 2021 08:42:30 GMT

Hi - there is obvious change of type of data you are doing and value change too...

you are saying that you have numerical field with values like: 202101,202102... which i take means that they are actual numbers? aren't they?

so when you you use Date# you are converting it to completely different values being: 44197 (for 202101), 44228 (for 202102)

It is basic understanding what Date# and Date function does to the numbers. This is all documented in help documentation. Please make sure you read examples from that help too...

https://help.qlik.com/en-US/sense/February2021/Subsystems/Hub/Content/Sense_Hub/Scripting/InterpretationFunctions/date_hash.htm

Then you may want to read this post about most common pitfals re date filtering in NPrinting to complement the topic https://nprintingadventures.com/2019/02/15/the-pitfalls-of-nprinting-filters-part-1-dates-and-duals

If this still does not help then next question is how you create your 202101, 202102 in Qlik script to understand what value is hidden behind the symbol value!

cheers

Re: problem with a date filter in qlik nprinting

P_qlik — Thu, 08 Apr 2021 08:51:28 GMT

Hi @Lech_Miszkiewicz , that formula works in qlik view and gives me 202104, the original field is a numeric field, so which formula should I use in nprinting filter to have the current month for this field?

Thanks

Re: problem with a date filter in qlik nprinting

Lech_Miszkiewicz — Thu, 08 Apr 2021 08:58:02 GMT

It does not matter what works in QlikView in this case...

QlikView may apply this formula as text search (not a numerical value search), on the other hand NPrinting is looking for numerical value (which i explained in my post I have given you link to in my post above)..

So again - what is the field value if you put it in the table and use expressions like:

text([YYYYMM (Anno - Mese)])
floor([YYYYMM (Anno - Mese)])

If values are the same for text and floor/number then your formula is simply wrong. At the same time I would say you just overcomplicated it as you could simply use:

if field is numerical:
- =Max([YYYYMM (Anno - Mese)])
or if field is text field then
- =Max(num#([YYYYMM (Anno - Mese)]))

Now I will say it again - we MUST know how this field is loaded and understand what datatype it inherits in order to give you 100% accurate answer.

cheers!

Re: problem with a date filter in qlik nprinting

Lech_Miszkiewicz — Thu, 08 Apr 2021 09:01:11 GMT

To add one more comment regarding syntax. I would also looked at other filters you are applying and depending if Max(your date) needs to return max for intersection (or not?) with other selections you may also use set analysis as an optional parameter:

if field is numerical:
- =Max({1}[YYYYMM (Anno - Mese)])
or if field is text field then
- =Max({1}num#([YYYYMM (Anno - Mese)]))

Re: problem with a date filter in qlik nprinting

JohanL74 — Thu, 21 Nov 2024 15:06:58 GMT

Good afternoon Lech,

i have studied this subject for some hours now (including your publication about 'variables and dimensions in NPrinting') and i still haven't found the solution to my problem.

I am stuck. Can you help me out, please?

Now I have a column in QlikSense containing YearWeek: 2024-1 until 2024-47 (text field).

I would like to filter the data in NPrining for 'last week' (46).

This is what i came up with: will this work in same way? Or will NPrinting not filter on text combined with formula like this?

I have also tried using variable vLastweek (=46) in Qliksense, but did not manage to get it working in NPrinting.

Can you show me the best way to go here, please?

Would be appreciated.

Thanks in advance,

Johan

Re: problem with a date filter in qlik nprinting

Lech_Miszkiewicz — Thu, 21 Nov 2024 23:30:21 GMT

Hi @JohanL74

What you have on screenshot will not work just because you used " " instead of ' '.

But lets get it straight:

How do you know that your field is a text field and not dual? Did you verify that and how? It is critical to confirm that in previewing field tags as well as in data load editor.
In your example you are using double quotes like this: "2024-", why? Text string always have to be in single quotes: '2024-' so I am confused as to why would you use " "
Why do you hardcoded 2024? Why Dont you use function like:
1. Replace(WeekName(WeekStart(Today(),-1)),'/','-')
2. Note that by using "replace" I am already converting that field to text string!!!
I would use explicit "Evaluate Value" filter for it, as advanced search can be affected by other filters - that is how advanced search works also directly in QlikView or Qlik Sense (based on current selection state vs full data set)

cheers.

If you