topic Re: Expression with modifier and if function in App Development

Expression with modifier and if function

Neha7 — Wed, 07 Feb 2024 23:04:18 GMT

Hello,

I'm trying to create a measure to get a count of all unique sales.

My table has columns like: OrderDate, SaleID,DeliveryDate, ProductID, Product Description, Status,Delivery Type,Country, State, City, Quantity, Price.

I have an existing master item to count unique sales with certain modifiers as below:

count(distinct{<
Country-={'Abcde'}

,DeliveryType={$(vExcludeCODDelivery)}
,Status={$(vExcludeReturnStatus)}

>}SaleID)

I need to modify this calculation to only consider SaleID with following criteria:

ProductID >= vMinProductID and ProductID <= vMaxProductID , where I am getting the variables filled in input box by User. Also, when no entries would be available in input box the min and max values should default to 0 and 100, respectively.

This is how I have defined the calculation to achieve new condition in a table called Details:

=aggr(

if(

ProductID>= (if(Len(Trim('$(vMinProductID )')) = 0,0,$(vMinProductID )))

and

ProductID<= (if(Len(Trim('$(vMaxProductID )')) = 0,6650,$(vMaxProductID )))

ProductID)

,ProductID)

I am unsure how to create a new expression taking all conditions into consideration.

I would appreciate all advises.

Thank you.

Re: Expression with modifier and if function

Nagaraju_KCS — Thu, 08 Feb 2024 06:03:13 GMT

may be this

count(distinct{<Country-={'Abcde'}, ProductID= {">=$(vMinProductID) <= $(vMaxProductID)"},
,DeliveryType={$(vExcludeCODDelivery)},Status={$(vExcludeReturnStatus)}>}SaleID)

Re: Expression with modifier and if function

marcus_sommer — Thu, 08 Feb 2024 08:31:03 GMT

I wouldn't use an if-loop for it else including it within the set analysis like suggested from @Nagaraju_KCS + defining there the default-value, for example with:

... ProductID= {">=alt($(vMinProductID), Default1)<= alt($(vMaxProductID), Default2)"} ...

Instead of alt() you may also use rangemin() and rangemax() to define the thresholds. Further by aware that a variable-call to a variable without any content respectively being EMPTY or NULL may fail by applying a $-sign expansion - in this case just skip it.

Beside of this it's often far more expedient not to implement such logic else to let the user select the wanted values in the field, just by applying a search like: >=MinValue<=MaxValue. Setting instead variable-values isn't easier or less work - it's just a detour with more complexity and less comprehension.