Conversion from if statement to pick and match function

[Aryasmank]

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 

M

#if-else  #pick-match  #optimization

[rwunderlich · Accepted solution]

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

[Aryasmank]

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

[marcus_sommer · Accepted solution]

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

[Aryasmank]

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

[marcus_sommer · Accepted solution]

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

- Marcus

[Aryasmank]

Thanks