Optimize this If-statement for performance

[robinwiman]

In our application we have different seasons:

171S	First season in 2017
172S	Second season in 2017
181S	First season in 2018
182S	Second season in 2018

etc....

In order to have the application change the actual season for us, we have implemented the following IF-statement in our expressions where needed:

If(WildMatch(SeasonCalc, vCurrentSeasonCalc)

This expressions is also accompanied by this line in our script:

LET vCurrentSeasonCalc = Right(Year(Today()-7), 2) & IF(VECKANRTMP < '27','1','2') & '*';

together they create a Season selecting in the expressions where we implement it.

Here's how an expression can look like with the If-statement included:

Sum ( If(WildMatch(SeasonCalc, vCurrentSeasonCalc),Fsgantal*_DateLastWeekAndBefore))

I've tried to move away from the If statement and create a more performance-friendly expression. Preferably a Set-expression. Is it even possible? I can't seem to figure out a way..

[swuehl · Accepted solution]

Maybe like

=Sum ( {<SeasonCalc = {"$(vCurrentSeasonCalc)"} >} Fsgantal*_DateLastWeekAndBefore)

[robinwiman]

I tried this in a table and it seems like it's working. The data output is the same as if I did filter it or use the IF-statement with the wildmatch.

Changing from the If-statement to the set-expression as you've explained, does mean I have to change over a 100 tables. Is there any other way I can double check that it will work in every aspect, since I'd like to find out sooner rather than later