Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:
Contributor III

## Conversion from if statement to pick and match function

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

Labels (3)

• ### SaaS

3 Solutions

Accepted Solutions

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

MVP & Luminary

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

MVP & Luminary

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

- Marcus

6 Replies

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

Contributor III
Author

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

MVP & Luminary

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

Contributor III
Author

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

MVP & Luminary

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

- Marcus

Contributor III
Author

Thanks

Tags
Community Browser