Set Analysis - Has anyone got a tutorial/examples of this?

[#1]

Hi all,

Is there a tutorial or examples of set anaylsis flying around? I need to be able to use today's date to calculate the accounting period that we are in and then using that answer, calculate how much sales we have using the answer as a filter.

i.e Today is July 2nd, The accounting period that it is in is July (There is an accounting period table with dates and the date 02/07/2009 has the Period July). and I have to sum up the values in that period.

I have 2 tables

Acc Period

Date Period

26/06/2009 June

27/06/2009 June

28/06/2009 July

..

..

24/07/2009 July

25/07/2009 August

Sales

Date Value

27/06/2009 100

28/06/2009 200

24/07/2009 150

25/07/2009 100

From the example there, I would expect my sum to equal 350.

Could you please help?

Thanks,

Mike

[#2]

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.

[Oleg_Troyansky]

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

[johnw]

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.

[#5]

Mike,

Check out this file from the Share QlikViews section; might help out with some basic Set Analysis examples:

http://community.qlik.com/media/p/64362.aspx

[Oleg_Troyansky]

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 pre-calculated 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

[prieper]

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 SET-Analysis 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

[#8]

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

[johnw]

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 pre-calculated 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.)

[anitamelbye]

This link is no longer working. Any suggestion?

http://community.qlik.com/media/p/64362.aspx