Nprinting - Filter field less than a variable

[leenlart]

Hello, 

I've been searching and testing different things, but I can't seem to get it! 

I have a report set up, with a graph showing values by year.  Now, I've been askded to filter the values by fiscal month, a year-to-date type thing.  I have fiscal month field (MOIS_FISCAL), that is 0 to 11 for each month based on our fiscal year start date (Sept = 0, oct = 1....).  And I have a variable that gives the current fiscal month vMoisCourantFiscNum. 

I would like to filter MOIS_FISCAL <  vMoisCourantFiscNum.  

In qlik sense, I set up a button to test my expression, and this works perfectly as expected : 

Now to Npriting, I've set up a filter to try and select my MOIS_FISCAL <  vMoisCourantFiscNum.  

I've tried it as above, with the syntaxe in Qlik (='<$(vMoisCourantFiscNum)'), chosing other options in the drop down list.  But my report won't render.  I get an invalid selections error.  

What am I missing ?  

Thanks for your help!!

[Lech_Miszkiewicz]

Hi @leenlart 

I suggest to study basics = help site. 

It says: https://help.qlik.com/en-US/nprinting/February2025/Content/NPrinting/ReportsDevelopment/Static-dynam... . You are trying to use "Evaluate value" option which also needs to return single value. In your case that obviously will not work as you try to use < sign which indicates potential array of values or search string.

In the Values drop-down menu you can select from:

• Value is: use this option if you want to select a string. For example, if you want to select USA in the Country field.
• Numeric value is: in QlikView and Qlik Sense, all field values are potentially dual values. A field value can have both a numeric value and a textual value. For example, a date that could have a numeric value of 40908 and the textual representation "2011-12-31". Use this option if you want to select a numeric value.
• Evaluate value: use this option if you want to insert an expression that needs to be evaluated. It must return a single value.
• Advanced Search: use this option if you want to select values using a QlikView or Qlik Sense advanced search.

So now when I explained the problem and what you did wrong I will just give you this "one liner" solution. You need to use in this case is Advance Search option for filter!

I also wrote blog post about it here about 6 years ago. It is old but describes flexibility of use of advanced search: https://nprintingadventures.com/2019/06/26/nprinting-filters-part-3-advanced-search/ 

More about filters here: https://nprintingadventures.com/category/nprinting/filters/ which you also may want to study as it explains a lot about data types which understanding of is critical when using NPrinting filters. I have answered a lot of questions about not working filters and most of the time users did not understand what the data type of the field they tried to filter on was. They also didn't understand what Dual() value type is in Qlik.

hope that helps. 

cheers

cheers

[leenlart · Accepted solution]

Hello Lech, 

Thank you for your reply.  I thought I would need to use Advanced search, but I wasn't able to get it to work.  I didn't understand why it wasn't working the same as in Qlik Sense.  

Here's what I did to get the filter to work properly : 

I had to add my filtered field in the search text.  

So instead of just ='<$(vMoisCourantFiscNum)' as I have in Qlik Sense, I put =MOIS_FISCAL<$(vMoisCourantFiscNum), taking out the ''.  

Why does this need to be repeated ?  I understand when we want to filter field A on field B < something, but I find this strange to filter A on filter A < something.  

Anywho, thanks.  

[Lech_Miszkiewicz]

HI @leenlart 

This is the same syntax you would use in Qlik Sense / QlikView. According to what I just tested you cannot just put < $(vVariable) or > $(vVariable) in search or at least it didnt work for me. Yes you can put actual numerical values but not variables using that syntax so the syntax which works for you would be the standard one you would normally use anyway in typical advanced search

[leenlart]

Well, technically, I was using a button to apply selections to test my syntaxe.  So that probably explains it.