Upper function failed

[emilyrabbit]

Hi , the below expression is ok.

however, if I use upper function, it failed.

Count(
{<[Order Type Mini]={"<200"},[Order Type Mini]={">0"},upper([Order Purchase Order Number])={'*TQBH*'},[Order Type Code]={'ZSTD','ZTTB'},[Fact_Type]={'FACT_ORDER'}>}
distinct [Order Document Number]
)

  why and pls help me check it .

[Mark_Little]

I would normally use functions like that is the script. would clear any confusion from your set analysis

[JandreKillianRIC]

Hi @emilyrabbit 

You cant use any functions on a field in Set Analysis - However you can add Functions inside the Set Identifier section. 

Create a new field in the script that has a UPPER value - Then you should be able to use Set Analysis normally. Please see link below 

https://help.qlik.com/en-US/sense/May2025/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAnalysi...

I also noticed that you have single quotes (') in your Search of the Purchase Order number. For literal string values we use single quotes (') and for searches we use double quotes ("). 

Regards

[Oleg_Troyansky]

Hi Emily,

There are quite a few issues here to unpack. Let's try to get to the bottom of it:

1. Your two search conditions for Order Type Mini replace each other. I suspect that only the second condition truly works and the first one is being ignored because it's replaced by the second. A better way of formulating these conditions is combining them together like this:

[Order Type Mini]={">0<200"}

2. Notice your use of single quotes vs. double quotes. Double quotes should be used for search conditions, and single quotes should be used for literals. So, Order Type Code and Fact Type values correctly use single quotes, while the search condition for the Order Purchase Order Number should rather use double quotes.

3. Now, to the core of your question - why the Upper() function doesn't work here. The Set Analysis syntax presumes that all conditions are applied to fields, not to expressions:

Field1 = {something}, Field2= {something}, ...

Your condition   is trying to make a search selection in an expression, rather than a field, and that's not permitted by syntax. However, you can try and use so called "fields on the fly". These are calculated fields that can be used in place of real fields. Fair warning - this may negatively impact performance of your app, if it's fairly large. The syntax for that is the following:

"=expression" = {something}         
 

So, the complete expression after all the corrections should look like this:

Count(
{<[Order Type Mini]={">0<200"},
"=upper([Order Purchase Order Number])"={"*TQBH*"},
[Order Type Code]={'ZSTD','ZTTB'},
[Fact_Type]={'FACT_ORDER'}>}
distinct [Order Document Number]
)

Allow me to invite you to my training session on Set Analysis and AGGR - at the Masters Summit for Qlik in Hamburg, or at my Qlik Expert Class in Vienna. See the links in my signature.

Cheers,

Oleg Troyansky

[emilyrabbit]

thanks.

[emilyrabbit · Accepted solution]

I finally find the correct. Thanks for everyone.

Count(
{
$<
[Order Type Mini] = {">0<200"},
[Order Purchase Order Number]={"=not WildMatch([Order Purchase Order Number],'*TQBH*')"},
[Order Type Code] = {'ZSTD','ZTTB'},
[Fact_Type] = {'FACT_ORDER'}
>
}
DISTINCT [Order Document Number])