I have this massive expression to work out a standard discount on 4 separate straight tables that is causing me a lot of problems when its being opened in Access Point or if its even opened like normal in QlikView (either doesn't load the table for about 15 minutes or crashes completely). It works fine without the expression so I know that is what is causing the problem but I don't know why its causing these problems.
Is there anything anyone can see in there that might be causing the problem?
Not sure why, did you try using your textbox and seperate these two expressions and see what value you are getting? Can you share your sample app if possible ?
I can't see anything obvious. You could try calculating the discount in the load script, it might take a bit longer to load but you should not have any performance issues for the user.
The slow response of these expression will probably caused from the used fields within them - if they are from different tables which are connected over multiple associations or even loosen tables it will create huge temporary tables on which the aggregations will be performed. So the first step will be to look and to adjust the datamodel.
Beside them your expression looked like a quote but both sum-parts are the same unless your check on [Std Disc%] - maybe you could find a more efficient way to calculate it.
I know this is a really late response, but it's worth the follow-up if for no other reason than to point out tools available for new developers in 2025 to help optimize their expressions.
To @marcus_sommer 's last comment, this expression has a lot going on that could be trimmed down, but I would submit that it can be trimmed down even further than the suggestion he offered.
Your sums are using the same set expressions and are aggregating on the same field, so they are equivalent. For brevity's sake, let's just refer to that sum as "A" everywhere for the time being. Let's also strip off the num() formatting for the moment so we can focus on the formula itself.
Substituting A in for the sums, your formula becomes:
1-(
(if(IsNull([Std Disc%]),1,(1-[Std Disc%])) * A)
/ A.
Now, the if-statement tells us we have two options:
Option 1: [Std Disc%] is null. Then that expression simplifies all the way down to:
1 - (1 * A) / A = 1 - A / A = 1 - 1 = 0.
Option 2: [Std Disc%] is NOT null. Then that expression simplifies all the way down to:
In other words, you could boil this whole formula down to:
alt( [Std Disc%], 0)
Now, here's why I'm taking the time to resurrect this nearly decade old thread: Google's Gemini 3.0 Pro AI model is actually the one who optimized this expression and figured all that out for me. Here is a link to that chat so you can see the prompt and its response (which was more than just optimizing the expression--it took the time to ask whether there was another intended purpose for the expression and whether perhaps there was a typo or flaw in the logic):
