I have a document where several of the objects are calculation on several hundred million records, and this is taking some time to calculate the charts each time a filter selection is made. This really becomes a problem when the user wants to make several selections because the charts automatically calculate with each selection.
Is there a way I can prevent the charts from calculating until the user has made all the selections they want?
My idea to do this is to set a calculation condition: vCalculate = 1, then set the variable vCalculate to '1' by user clicking a button. But how do i get that vCalculate to automatically reset to 0 when the user starts to make new selections? I have seen examples where there is a second button for the user to reset the variable but that would be frustrating to the user to have to remember to do this manually each time he wants to make new selections.
You could try placing copies of all the listboxes you need onto a dedicated filter sheet with no charts. Activate this sheet with a button on your analysis sheet and when filter sheet is activated your charts will not recalculate until the analysis sheet becomes active again.
Interesting idea... I'll give that a shot. My only concern there goes back to usability for the client - I think they will want to be able to see the current calculations while they're making new selections. But then again maybe I'm trying to solve for something the user won't even care about. I'll report back on how it goes. Thanks!
I always first encourage people to tune the document so auto-calculation becomes a non issue. All that said, attached is an example that collects "proposed" selections in an alternate state until the user presses "Apply".
(Example deleted in favor of the cookbook example in the subsequent reply)
The first thing I tried was using the Document Analyzer to help me tune the data model as much as possible, and I was able to reduce the amount of data in the doc by over half, but it's still 6.8 GB and has almost 700 million rows of data. The calculation speed didn't change much.
I saw a slight improvement when i created variables to replace some expressions that were used more than once, but there aren't a lot of places to do that.
I've also tried minimizing as many reports as I can, but it's the most used ones that are taking the longest to calculate. They aren't even very complex expressions, I guess it's just the number of records.
I like your idea of using Alternate State to hold the filters, so I'll try that and see if the user is satisfied with that approach. Thanks.