Evaluate Value filter not working

[ssha1983]

Hi

I have a field in my QV document called "Current Shipment Season" that always has just one value.

When I use expression"=only([Current Shipment Season])" in my document, I get 2021-2022.

However, when I try to use this expression as part of the evaluate value and use it in a report, the filter is not getting applied.

When I change "Evaluate Value" to Num Value and hardcode "2021-2022", the filter works fine.

Can someone tell me what I am doing wrong please.

Thx!

[Lech_Miszkiewicz]

Hi.

1. What happens when you open QlikView and do following:

• Clear all selections
• make sure there is "no always one selected  value" selection applied
• make sure there are no hidden fields selections applied
• create a textbox and put in it your formula
  • does it still return only single value?

The above bulletpoints assure that this is what would happen in QlikView when NPrinting would try to create such filter.

2. Another thing which surprises me is when you say: When I change "Evaluate Value" to Num Value and hardcode "2021-2022", the filter works fine.  I will believe that "Value is"  option would work, but I don't believe that "Numerical value is" works in that case. So that needs to be clarified. 

3. Another question I have is what is actually your Fiscal Year field in QlikView. How is it created in load script, what datatype it has. If it is a dual it may be that you are trying to apply text filter on value which needs to be numerical. As a test in QlikView create a table where as dimension you put FiscalYear and as measure num(max(FiscalYear).) Does your measure show different value than dimension? Also you can check in table viewer by hovering over your mouse above a filed. More on that topic here: https://nprintingadventures.com/2019/02/15/the-pitfalls-of-nprinting-filters-part-1-dates-and-duals/

Having answers to above questions will definitelly help us understand what may be wrong with your current approach.

cheers

[ssha1983]

Hi Lech

Thanks for your reply.

Answer to your questions below

1. I did all the steps you mentioned. I can confirm I get the correct Season "2021-2022" is selected (picture 1 attached)

2. I can confirm I used Num Value and not Value. (I foudn this strange as well). Picture 2 attached

3.  The FiscalYear is calculated ina qvs script as yearname(Date,0,8) as FiscalYear

I also tried creating a table as per your message and I got as attached in picture 3. Not sure what I am doing wrong.

[Lech_Miszkiewicz]

Ok - so that explains almost everything.

As you can see on last screenshot your 2021-2022 is not a text but a dual as you can expect by looking at definition of the function you have used - which is critical in this case. 

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFun...

So for 2021-2022 you effectively have numerical value of 44409 which actualy is 01/08/2021 date

That means that NPrinting is expecting value in the numerical format which is 44409

So now the question is if your [Current Shipment Season] was created using the same "Yearname" function? meaning that you could do filter like:

=Only(Floor([Current Shipment Season]))

Can you confirm how your [Current Shipment Season] field is created in your load script? Is it text or dual?

thanks

Lech

[ssha1983]

Right. I think I get what your saying.

I am using the attached sql script to calculate Current Shipment Season (varchar)

[Lech_Miszkiewicz · Accepted solution]

So that will not work.

As your FiscalYear in QlikView is having a Dual type data and your [Current Shipment Season] is a Text when its loaded to QlikView. 

NPrinting is expecting numerical component of the value which with your current SQL statement is not getting as it just gets concatenated text string.

Those 2 values on surface look the same, but are not the same.

More about Dual() here: https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/Formatt...

Note that most of "date" functions are returning Dual type, example Month() function can return "1" as number and "Jan" as text. NPrinting in such case would expect to get "1" value in the filter.

So as an quick and dirty workaround can you check for me if this would work for you in evaluate filter?

=Floor(MakeDate(num#(Left(Only([Current Shipment Season]),4)),8,1)) 

I assume it will work as it will do fiollowing:

• take 2021 text using Left(field, 4) function
• convert that to number using num# function
• make a date for date with 8 as month and 1 as day
• and convert it to integer using floor function

If that works then you have mystery solved.

cheers

[ssha1983]

That worked. Thank you.

[Lech_Miszkiewicz]

Hi @ssha1983 

We kind of focused on describing all details around how "Evaluate Value" component work and what values it expects. 

But frankly If you would like to do nothing and would like to use your formula as =Only[Current Shipment Season] you could still do this with "Advanced Search" option from the dropdown when creating filter. 

Since it is a search filter it works differently and is not so much focused on data type of field. 

I think it is important to mention this here jsut to clarify that such option also existed. 

cheers

Lech

[ssha1983]

Cheers