selection error

[emilyrabbit]

HI ,

I want to compare the selection year's order amount with the selection year-1 order amount.

So， I use below variable:

v_maxyear_order=if(max([Order Year])<=year(today()),max([Order Year]),year(today()))

v_lastyear_order=v_maxyear_order-1

and in the kpi:

select year=Count(
{
$<[Order Year]={$(v_maxyear_order)}
,[Order Purchase Order Number]={"=not WildMatch([Order Purchase Order Number],'*TQBH*')"}
>
}
DISTINCT [Order Document Number])

select year-1=Count(
{
$<[Order Year]={$(v_lastyear_order)},
[Order Purchase Order Number]={"=not WildMatch([Order Purchase Order Number],'*TQBH*')"}
>
}
DISTINCT [Order Document Number])

the weird thing happens:

if I choose [order year]=2025, we can see the select year=6532 (current select year=2025), and the select year-1(current select year-1 should be 2024)=50

if I check [select year-1],below is the expression: it seems ok ,but the [select year-1] (should be 2024's result is not ok)

if if I choose [order year]=2024, we can see the select year(current select year=2024)=14548 , and the select year-1(current select year-1 should be 2023)=140

if I remove the  [order year] selection , the result is good.

I am curious , and the 2024 order should be 14 548. why?

[Oleg_Troyansky · Accepted solution]

Hi Emily,

I checked the screenshots, and I realize that my initial guess was incorrect - your variables work as expected.

I think the issue is in the "conflict" between the selected "Order Year" and the two Set Analysis Filters, one of them also being "Order Year".

So, your first filter [Order Year]={$(v_lastyear_order)} is replacing the selected year with the calculated  value from the variable. So far so good.

The second filter uses an Advanced Search (aka "expression search") - that is a "strange animal". In essence, it contains its own aggregation with its own Set Analysis that doesn't necessarily follows the selection from your first filter. So, while the first filter may be selecting year 2024, the second filter could be working with a data set where the selected year is 2025. Surprisingly, the result is not zero, but I'd need to dig deep into your data to understand why us that.

So your solution could be one of the following - either equip your advanced search with a proper Set Analysis of its own, or replace Advanced Search with a simple search. I'd try something like this:

{
$<[Order Year]={$(v_lastyear_order)},
[Order Purchase Order Number]-={"*TQBH*"}
 >}

This is a simple search, and it doesn't carry the same issues as the advanced search. I remember from your earlier questions that you were trying to make this selection ignore upper/lower case, and that's what wildmatch was used for. Try using a field on the fly instead:

{
$<[Order Year]={$(v_lastyear_order)},
"=UPPER([Order Purchase Order Number])" -={"*TQBH*"}
 >}

Cheers,

Oleg

[emilyrabbit]

HI ,I have tried your way. It work. I just curious why the wildmatch function cause the disorder.

[emilyrabbit]

Hi  Lennart_mo, Sorry I didn't get you point (I know every single word, but confused the sentence ) . I tried the below expression, and it wroks , show the correct result. I am curious,  it seems wildmatch used in set analysis will bring trouble ,right ? Thanks for any information. 

Count(

    {

        $<[Order Year]={$(v_lastyear_order)},

        ,[Order Purchase Order Number]=-{"*TQBH*"}

        >

    }

    DISTINCT [Order Document Number])

[emilyrabbit]

Hi Oleg_Troyansk，thank you so much for your detailed explanation. I totally agrees with you , wildmatch in set analysis looks like a "strange animal" .

after test , the below expression(you suggest)is good:

{
$<[Order Year]={$(v_lastyear_order)},
[Order Purchase Order Number]-={"*TQBH*"}
 >}

So, in conclusion , afterwards , I should avoid using wildmatch in set analysis ,right ?  To prevent the selection issue . Thanks.

[rafaelencinas]

HI @emilyrabbit 

The reason is:

- When you filter over SetAnalysis you have to take care with expression. It isn't work like "where expressions" in the script.

- SetAnalysis is based on "Set Theory"

"You want to count orders that not contains TQBH"

-= ( minus equal ) - unlike 

Instead of

[Order Purchase Order Number]={"=not WildMatch([Order Purchase Order Number],'*TQBH*')"}

Try

[Order Purchase Order Number]={"=WildMatch([Order Purchase Order Number],'*TQBH*') = 0"}

But

If the field [Order Purchase Order Number] is a key field, maybe it will not work. 

bye!

[marcus_sommer]

A "real" set analysis is a selection respectively evaluating the conditions on a column-level. As far as something like your {"=function(parameter)=result"} is used it means to apply an if-loop evaluation against the row-level - it's just pressed within a set syntax. It's a mix of quite different approaches and may add more complexity and confusion as benefits.

Logically simpler are approaches like:

if(Condition, SetExpression)

or

SetExpression * Condition

Personally I would tend to create an appropriate flag-field in the data-model which contains the Purchase Order Type and using it as filter.

[Oleg_Troyansky]

Hi Emily,

The wildmatch could maybe cause a problem (I never tried it in this context), but in this case it's not about specifically the wildmatch - it's the difference between Simple Search (aka Numeric or Alphanumeric search conditions):

Field1 = {"*ABC*"}   or Field1 = {">1000<2000"}

and the Advanced Search (aka Expression Search):

Field1 = {"=expression"}

The Expression Search contains an implicit aggregation with its own "set". When its "set" is not specified, it would be the "Default State", or Current Selections in simple words.

So, Expression Search is very powerful and can be used when needed, but it might be hard to deal with when the Set Analysis expression overrides the Current Selections made by user. In your example,  user selections in the field [Order Year] are being replaced within Set Analysis in your first filter, and that causes a conflict with the implied set within the Expression Search in your second filter.

I hope that explains the issue. Come to my class to learn more about Set Analysis.

Cheers,

Oleg Troyansky