How can I apply a greater than or equal from an integer field in Set Analysis?

[ilknura]

Hello everyone,

I must apply greater than or equal conditions between my two integer fields in set analysis.

But on the first try, turning 0 value.
also, the second try is turning the average total ordered_amount.

How can I solve this issue?

I tried this (the first try);

Count({<Shipped_Amount={"<=Ordered_Amount"}>} distinct Order_ID)

and this (the second try);

Count({<Shipped_Amount={">=$(= $(vAmount))"}>} distinct Order_ID)

vAmount: sum( Aggr(avg(Ordered_Amount),Order_ID,Item_Code))

I am waiting for suggestions, thanks in advance.

[PrashantSangle]

Hello, 

try below

Count({<Shipped_Amount={">=$(vAmount)"}>} distinct SIPARIS_FIS_NO)

in your variable add = in that

vAmount variable is like 

=sum( Aggr(avg(Ordered_Amount),Order_ID,Item_Code))

Regards,

Prashant Sangle

[ilknura]

Hi,

thank you but I tried this already it didn't work. I guess that kind of syntax doesn't work on variables.

Count({<Shipped_Amount={">=$(vAmount)"}>} distinct Order_ID)

this is working;

Count({<Shipped_Amount={">=$(= $(vAmount))"}>} distinct Order_ID)

but the variable is not giving me the true amount.

[PrashantSangle]

the output of $(vAmount) is completely depend on how you define the variable?

Can you share the snip of variable definition? 

[Cascader]

try:

Count({<Shipped_Amount={'>=$(= $(vAmount))'}>} distinct Order_ID)

[sbaro_bd · Accepted solution]

Hi @ilknura ,

If your objective is to count orders whose the shipped amount is below the ordered amount, I can suggest you to create a flag in your script and use it in a simplified expression : 

• Create this flag in your script 
  

  IF(Shippered_Amount < Ordered_Amount, 1,0) as Flag​

• Use the flag is your set analysis expression
  

  Count({<Flag={1}>} distinct Order_ID)​

  Regards.

[ilknura]

This is working, thank you! @sbaro_bd 

[marcus_sommer]

What do you want to do isn't possible - at least not with a classical set analysis which is a column-level evaluation and your described requirement needs a row-level evaluation. This means you will need an if-loop to make this kind of query. This might be done with set analysis syntax - but it remains an if-loop - maybe like this:

Count({< Order_ID ={"=Shipped_Amount<=Ordered_Amount"}>} distinct Order_ID)

Depending on the data-model and the object-view you may need further measurements, like an aggr() to force the comparison to consider the needed dimensionality.

If any possible follow the suggestion from @sbaro_bd and making the essential work within the data-model. 

[ilknura]

this is how I define the variable

[ilknura]

Thank you for your detailed feedback. I understand now how is that not possible.

I solved that with @sbaro_bd  's suggestion.