I am trying to understand if it is reasonable to use set logic with 950,000 rows of data and expect chart performance to be usable as a user changes values that changes the sets. This would be with from the QVS with Ajax client.
Generally speaking, 1 Mil. rows of data is not such a large volume to worry about (unless you meant 950 Mil. rows, which is a different story)
On the other hand, Set Analysis, when used correctly, is the best performing way of implementing conditional aggregation. What I mean by "used correctly" is when the SA conditions are kept simple, free of IF() statements inside the conditions (I've seen those, too), free of long lists of possible values and other overly complex constructions that can typically be avoided with a little bit of scripting.
As long as the data is linked and Set Analysis conditions are simple, you shouldn't have any problems...
Well, any method is "reasonable". SOme are more elegant, and some are better aligned with the idea of "simplicity":
- Function MonthStart() is perhaps more elegant than using formatting with the fixed Day
- My personal preference is to calculate a set of flags for most obvious date selections - Current Year/Quarter/Month/YTD, and Prior Year/Quarter/Month/YTD, which makes your SA condition totally crisp and simple:
{<CurrentMonthFlag={1}>}
- You are summarizing 2 fields... If there is any chance that one of them might contain a null(), you probably need to use Rangesum() to deal with accidental nulls.
- If performance seems to be an issue, why not summarize the two fields in the script, limiting the expression to a single field summary
- If the two fields are coming from 2 different tables, that could cause a huge performance hit - QlikView will have to do a lot of extra processing before it can calculate this expression.
Other than that, this expression doesn't look like a huge performance problem...
