
Set Analysis  Has anyone got a tutorial/examples of this?
Neil Miller Jul 2, 2009 10:03 PM (in response to michaellee1)I'm not really following your data there, so I'll just give a few good places to look.
1. Right here. On the right hand side of the forum, you should see Popular Tags. Set Analysis is the largest. Click on that and get a list of all forum posts tagged as Set Analysis. Searching for Set Analysis and Date should get you some good examples.
2. The Reference Guide. There is now a Set Analysis section at the end of Book II. That's how I learned the basics. I have it printed out and regularly refer to it when exact syntax slips my mind.
I'm assuming you already have some Set Analysis going, maybe you could post what you have so far.

Set Analysis  Has anyone got a tutorial/examples of this?
Oleg Troyansky Jul 2, 2009 11:12 PM (in response to Neil Miller)Help section also has a nice article about Set Analysis with many helpful examples.
Looking at your requirement, though, I think you should start at the script level, not within the expression. When you reload the data, it's very common to capture the "Current Date" into a variable. It's also pretty common to calculate Current Fiscal Year and CUrrent Fiscal Month.
Many people stop there and then load their logic into their expressions  using either IF statements or Set Analysis. As a result, expressions become very hard to read.
I usually claculate a set of flags  for example, Current_Month_Flag, YTD_Flag, Rolling12_Flag, etc... Each flag gets a 1 when the condition is true or null() when it's false.
With the flags, you can construct your statement like this:
"Current Month Sales" = sum(Sales*Current_Month_Flag)
or, if you really want to use Set Analysis, the expression is still pretty simple:
"Current Month Sales" = sum( {$ <Current_Month_Flag = {1} >} Sales)
cheers,
Oleg

Set Analysis  Has anyone got a tutorial/examples of this?
John Witherspoon Jul 2, 2009 11:29 PM (in response to Oleg Troyansky )I wouldn't use sum(Sales*Current_Month_Flag). It might take the fewest characters to type, but it isn't particularly clear from a maintenance perspective, and isn't particularly fast from a performance perspective.
Marginally faster and (to me) much more clear is sum(if(Current_Month_Flag,Sales)).
The fastest but somewhat less clear (to me) approach is set analysis with the flag as mentioned, sum({<Current_Month_Flag={1}>}Sales). Since it is less clear, it may not be worth the headache if your data set isn't huge. But you may benefit from simply standardizing on the fastest approach, so that the same approach is used across all applications regardless of necessary performance in each.



Set Analysis  Has anyone got a tutorial/examples of this?
Community Administrator Jul 3, 2009 7:22 AM (in response to michaellee1)Mike,
Check out this file from the Share QlikViews section; might help out with some basic Set Analysis examples:

Set Analysis  Has anyone got a tutorial/examples of this?
Oleg Troyansky Jul 3, 2009 8:38 PM (in response to Community Administrator )John,
I can't quite agree with your reasoning... Clarity is a matter of personal taste (once you get used to using flags, it's pretty darn clear to me), but performance can be measured...
Prior to Set Analysis, multiplication produced much faster results than IF formulas. In one extreme case, I managed to reduce calculation time for a large pivot table from 88 sec. (!) to less than a second, just by replacing a number of IF formulas with precalculated flags.
Using Set Analysis "might" be even faster than multiplication, but I haven't had a chance yet to test it out on a large data set.
cheers,
Oleg

Set Analysis  Has anyone got a tutorial/examples of this?
Peter Rieper Jul 5, 2009 11:33 PM (in response to Oleg Troyansky )Fully agree,
described way of multiplication with either 1 or 0 has been the fasted way in SQL and QV as well. Do consider the SETAnalysis more a way of individual calculation "on the fly", whereas assigning a flag in the script requires already a clear definition of the final product....
/Peter

Set Analysis  Has anyone got a tutorial/examples of this?
michaellee1 Jul 6, 2009 3:10 PM (in response to Peter Rieper)Thanks for all your help. I will have a look through to see what is the best solution for me and post any updates on here.
Thanks,
Mike

Set Analysis  Has anyone got a tutorial/examples of this?
John Witherspoon Jul 13, 2009 7:16 PM (in response to Peter Rieper)
Oleg Troyansky wrote:Prior to Set Analysis, multiplication produced much faster results than IF formulas. In one extreme case, I managed to reduce calculation time for a large pivot table from 88 sec. (!) to less than a second, just by replacing a number of IF formulas with precalculated flags.
Using Set Analysis "might" be even faster than multiplication, but I haven't had a chance yet to test it out on a large data set.
Peter Rieper wrote:described way of multiplication with either 1 or 0 has been the fasted way in SQL and QV as well.
I've been trying to measure performance of the various approaches in this thread:
http://community.qlik.com/forums/p/17188/69900.aspx#69900
I recently improved my testing method, and came to a more detailed conclusion about IF vs. multiplication. IF vs. multiplication performance appears to depend heavily on the situation. Here are my overall conclusions for version 8.50.6231.5:
 Set analysis is MUCH faster than IF or multiplication.
 Using a 1/Null flag is faster than a 1/0 flag or not using a flag.
 IF is faster than multiplication when using flags.
 Multiplication is MUCH faster than IF when NOT using flags.
(Edit: Oleg, to expand on this a little, if you replaced a complicated IF expression with a multiplication by a 1/Null flag, a large performance improvement is to be expected. But I would expect you to see slightly better performance still by replacing the multiplication with an IF check of the same flag, and much better performance still by replacing the multiplication with a set analysis expression using the same flag. As best I can tell, it's the flag itself offering the performance improvement, not the multiplication. Unless you're saying you were using a flag already, in which case I can't account for our differing results, though using different versions might easily explain it.)



Set Analysis  Has anyone got a tutorial/examples of this?
Anita Melbye Apr 7, 2010 7:01 AM (in response to Community Administrator )This link is no longer working. Any suggestion?

Set Analysis  Has anyone got a tutorial/examples of this?
Community Administrator Apr 7, 2010 12:27 PM (in response to Anita Melbye)Anita,
Please try this link instead: http://community.qlik.com/media/p/82595.aspx

