topic Re: Conversion from if statement to pick and match function in App Development

Conversion from if statement to pick and match function

Aryasmank — Tue, 08 Nov 2022 14:09:36 GMT

Hi all,

I have to change the if statement to the pick and match function, but there is "greater than" in if statement. I'm unable to find out how to do that.

Below is my expression :

if([Date] = Date($(MaxDate)), $(tostate),
if([Date] > Date($(MaxDate)),
if(linest_m( total aggr(if(count(id_CR) ,count(id_CR)),[Date] ),[Date] ) >=0,

$(tostate)- RangeSum( Above(linest_m( total aggr(if(count(id_CR) ,count(id_CR)),[Date] ),[Date] )
*[Date.FiscYear.YearMonth]
+linest_b( total aggr(if(count(id_CR) ,count( id_CR)),[Date] ),[Date] )
,0,(((year([Date] )*12)+month([Date] ))-((year(Date($(MaxDate)))*12)+month(Date($(MaxDate)))))
))

,
$(tostate)- RangeSum(above($(laststate)
,0,(((year([Date] )*12)+month([Date] ))-((year(Date($(MaxDate)))*12)+month(Date($(MaxDate)))))
))

)))

If anyone has any inputs please share.

Thanks

#if-else #pick-match #optimization

Re: Conversion from if statement to pick and match function

rwunderlich — Tue, 08 Nov 2022 14:38:04 GMT

To code your Match using varying expressions, use "-1" (true) as first argument and code it like this:

Match(-1, [Date] = Date($(MaxDate), [Date] > Date($(MaxDate), etc...

Why are you converting from If() to Pick(Match())? if it's for optimization my experience is that pick(match()) performs the same as if().

-Rob

Re: Conversion from if statement to pick and match function

Aryasmank — Wed, 09 Nov 2022 06:02:58 GMT

Hi ,

Thanks for the response,

Yes, I'm converting it for optimization , as you said , pick(match()) performs the same as if(), can you please suggest any other option for optimising the above expression . It will really helpful.

Thanks,

Mayank

Re: Conversion from if statement to pick and match function

marcus_sommer — Wed, 09 Nov 2022 10:52:04 GMT

I do agree with Rob that there won't be a significantly performance difference between if() and pick(match()). But you may optimize your calculations itself. For example, the part of: if(count(id_CR) ,count(id_CR)) might be just replaced with count(id_CR).

Also, the continuous period-counter of (year([Date]*12+month([Date])) could be already generated within the script and the period-counter to the variable outsourced within another variable and maybe even also created within the script (if the max-date is always a quite fixed value like today() or similar).

Whereby I wouldn't expect a much better performance from these measures because the general approach is quite resource expensive - nested if-loops with aggr() within interrecord-functions won't be never really fast. Therefore you may need a re-design of your object/view and/or the underlying data-model.

- Marcus

Re: Conversion from if statement to pick and match function

Aryasmank — Thu, 10 Nov 2022 06:10:39 GMT

Hi, thanks for the input ,

if possible, can you please explain, little more , the second para about continuous period-counter . It will really helpful.

Note: the MaxDate is also contain the nested if expression

thanks ,

Mayank

Re: Conversion from if statement to pick and match function

marcus_sommer — Thu, 10 Nov 2022 17:11:53 GMT

Something like: year([Date]*12+month([Date]) could be added within a master-calendar and would then be available for all calculations.

- Marcus

Re: Conversion from if statement to pick and match function

Aryasmank — Fri, 11 Nov 2022 07:12:56 GMT

Thanks