Using a calculated value in below() to create a different size sliding window in a chart
Lyndon Nelson Feb 27, 2013 12:02 AMWe are having a terrible time trying to get a sliding window of days (which can vary for each row in the table) to calculate efficiently. We have been able to get it to calculate correctly using multiple IF statements, it will not scale well up to a maximum of 1097 IF statements. The attached example demonstrates a couple ways of using the inefficient IF statements (which display the correct answer), and what I think would be the desired expression (which returns the correct answer).
The short version of the problem is that I need the expression for OnBuff to read and use the OnTRR value in the below() statement (see example.qvw). The displayed value in the OnBuff column (which is incorrect about half the time) should equal the value in both the OnBuff99 and OnBuffTest columns (which are correct in every instance). I have tried every way I can think of to call the value in the OnTRR column, but can never get it to display the correct answer in OnBuff for every row.
Use the Test1 bookmark and go to the Buffer sheet to see the output data, although you can pick a single Site with a handful of PartNos and you should see the problem. Ignore the results for any OnTRR that is over 60, as I didn’t put IF statements in for over that number because it slows the app down too much.
More information for those who want it.
The client needs to calculate the buffer for 1000 part numbers, and the value of the OnTRR can be as high as 1097. This makes a multiple layer of IF statements very inefficient, at least as I have them currently written. I have tried many different combinations of variables, IF statements, Set Expressions, dollar expansions, etc, but just can’t seem to get anywhere close to 1000 part numbers to return an answer in any reasonable time frame.
I have limited the OnBuff99 and OnBuffTest IF statements to use an OnTRR of 99 and 60 respectively to prevent the app from becoming extremely slow. Even right now it takes close to a minute to calculate for 40 part numbers which means taking about 25 minutes to calculate for 1000 part numbers. But that’s not all – the production app will also need to calculate an OffBuff and an AggBuff using OffTRR and AggTRR (which aren’t shown here but will use the same type of expression). That pushes the time to over an hour for the answer to return which isn’t acceptable.
A side note about the locked ShowZeroPoints? Listbox – for OnBuff to calculate correctly, we need to have a row returned for every date even if the QTY is zero so we examine the OnTRR number of calendar days. So we use the Set Expression in the OnBuff calculation to get a QTY for all days, but have it locked to N so that standard expressions don’t see all the extra data.
Hope this all makes sense – we could really use your help on this as it has us puzzled. If you think you have an answer, please incorporate it into the example.qvw and send it back – I will check it out in the production app to see if the performance is better.
Thanks.

example.qvw 320.2 K