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]

Hi Emily,

It appears to me that your variables may not be working as expected. To help me diagnose, can you please share a few more screenshots:

- the definitions and the values of both variables (especially the previous year) from the variable overview.

- the full view of the Expression Editor for the Prior Year expression, when either 2024 or 2025 is selected as the current year.

Cheers,

Oleg Troyansky

[Aditya_Chitale]

@emilyrabbit ,

If you are analyzing the variables in a KPI, have you tried changing the number formatting to Measure Expression in the KPI options ? Also, when creating the variable v_lastyear_order,  add a $ sign before the variable v_maxyear_order

something like: 

v_lastyear_order=$(v_maxyear_order)-1

Let me know if the suggestions worked.

Regards,

Aditya

[emilyrabbit]

HI ,I have attached more screenshot ,could you pls check?

[emilyrabbit]

HI I have tried, but the result is still wrong.

[lennart_mo]

Hi @emilyrabbit,

I think the problem lies in the second part of your set expression:

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

The Wildmatch seems to only use the [Order Purchase Order Number] Values available for the current selection. Adding '{<[Order Year]=>}' as follows should work.

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

Hope this helps!

Regards,

Lennart

[emilyrabbit]

Hi ,I have added some screenshot in the question. could you pls help checking it ? Thanks.

[emilyrabbit]

HI Lennart , nice to meet you .I am very interested on your logic. because normally, 

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

part1 and part2 is dependent. Can you pls explain it more detail ? I really want to understand them clearly and more. Thanks so much.

[lennart_mo]

Hi Emily, 

While part1 and part2 do depend on each other, the expressions setting the values should only be affected by the applied selections. 

I think in this case it's easier to understand looking at the first expression. Were you to set the Order Number via the set analysis, to a value only occuring in 2024, v_maxyear_order should still evaluate to 2025 in the same set expression. 

On the other hand, were you to actually select the same Order Number, v_maxyear_order should now return 2024.

[rafaelencinas]

Hi @emilyrabbit !

It could be a little bit simple..

Orders:
LOAD *
, Year("Order Data") as "Order Year"
;
Load * INLINE [
Order Purchase Order Number | Order Data | Order Document Number
ABC123 | 01/01/2024 | ABC123
ABC354 | 01/01/2025 | ABC354
ABC356 | 01/01/2025 | ABC356
TQBH123 | 01/01/2024 | TQBH123
TQBH354 | 01/01/2025 | TQBH354
](delimiter is '|');

Actual Year

Count({
$<[Order Year]={"$(=Max([Order Year]))"}
,[Order Purchase Order Number] -={"*TQBH*"} >}
DISTINCT [Order Document Number])

Last Year

Count({
$<[Order Year]={"$(=Max([Order Year])-1)"}
,[Order Purchase Order Number] -={"*TQBH*"} >}
DISTINCT [Order Document Number])

Bye!