Do you normally succeed CREATING and DOCUMENTING a Set Analysis expression/formula like the following one in less than two minutes?
Sum({$<Year=,Month=,CustomerCountry=P({1<CategoryName={'Confections'}>}SupplierCountry),Customer=P({1<Year={$(=max(Year)-1)}>}Customer)>}Sales)
I do normally not! And even if I would, after two weeks I cannot remember why I have created this statement like this …
That’s why I have created the “Set Analysis Wizard for QlikView” 
Introducing the Set Analysis Wizard for QlikView
So let’s directly jump to the result in the “Set Analysis Wizard for QlikView”:
If you open the above mentioned expression in the wizard, you’ll see the following:
And if you click on the tab “Generated Expression (+ Comments)” you’ll see the following:
You are welcome to add/change set modifiers at any time:
So what’s happening here?
By using the “Set Analysis Wizard for QlikView” you’ll now have a tool which
- enables you creating Set Analysis Expressions in WYSIWIG mode (!!!)
- Event the most complex Set Analysis Expressions will be documented automatically
- optionally you can save the expression on the server and edit/change/enhance it at any time
- furthermore you can share the created expression with others
Step by Step Instructions
Hopefully a step by step instruction is not necessary.
I have worked hard to create a self explanatory user interface (but if I did not succeed in your opinion, please do not hesitate to tell me!)
So just give it a try:
Load the example mentioned at the beginning of this article
Examples
I have added some examples which can be directly opened in the wizard:
All examples can be opened directly in the wizard
Sharing your Set Analysis Expressions
Feel free to share your results.
Just click on “Save result for later usage and sharing” and two things will happen:
- The Set Analysis Expression will be saved on the server and you can come back later an re-edit it at any time.(The link for re-opening the Set Analysis Expression will be added to the comment of the expression automatically)
- Furthermore you can share your expression with colleagues, in discussion forums, and so on …
Enable "Save result for later usage or sharing" and "Share Expression"
Roadmap
So far this is the first beta version.
I have not included QlikView 11 support (Alternate States) so far, but for sure, this is the next update you can expect …
Feedback, Improvements, etc.
Please give me feedback:
- Do you like the Set Analysis Wizard?
- What is missing?
- Is it easy to use?
- etc.
Stefan WALTHER
77 Comments
Wow! Good stuff Stefan! Thanks!
Hi Stefan,
Thank you very much for creating this awesome tool.
I especially like the feature of saving the expression on the server, being able to come back and change it after some time!!!
Do you plan any further wizards like this one or the nested-ifs-wizard?
;);)
Cheers, V.
Thanks Vladimir,
first of all I will concentrate to improve the Set Analysis Wizard a little bit (especially by adding the QV 11 functionality of Alternate States), so there are no further wizards in the pipeline
… but some other ideas …
Regards
Stefan
Would it be possible to add a reverse lookup for expressions?
BTW, your tool is excellent and should be incorporated into the Qlik itself.
Look like a wandefull tool that can be very usefull and timesaving.
Thanks a lot !!
Brilliant! I suggested to QlikTech when they introduced Set Analysis that they needed such a wizard built into QlikView. I’m glad to see someone took the initiative and got it done.
Cooooooooooooooool … this really helps me a lot. The last two hours (it’s Saturday!!!) I have used your wizard to create several set analysis statements => the first time I understand the logic behing set analysis I am relatively new to qlikview (3 months) … it’s just great! would be nice if you could add more examples to your wizard because they are a really good starting point!
Have a nice weekend and greeting to austria! Angie
Nice one!
Would be nice if you could also publish a QV-file with all the expressions used in your examples!
Greetings, Ebony
Hi Ebony,
yes, absolutely. I will probably do that in the future but before creating such an application (or publishing my existing test-application
) I first want to expand the examples section by adding a lot of new examples, dealing with more complex set analysis expressions.
Regards
Stefan
Hi Stefan,
Thank you very much for sharing this awesome tool with us.
Set Analysis is very powerfull but hard to understand the goal of an existing expression even if you did create it yourself !!!
Generation documentation while creating the expressing is a briljant concept.
This is the wizard we need in QlikView ASAP.
Regards Daan
Hi Daan,
yes, the idea of generating the comments automatically is IMHO one of the key features which is really important to be able to re-edit the expressions later on …
Regards
Stefan
Hi Stefan,
Very good! Nice tool for people who don’t do a lot of set analysis.
Regards,
Stephen
This is awesome, as always! Thank you very much for sharing this with the world!
Hello Stefan,
Thanks for the Awesome work done for developers new to QV world to know Set Analysis.
Regards
Ashwin Pagare
Stefan,
Nice job! I am sure this will get used by thousands of people….
Ed
Hi, Stefan
I am a new born baby for QlikView. I have read your Tips related article. Now I am seeking detailed document regarding Set Analysis & Aggr function can you suggest any on or can you provide
Thanks & Regards
Anil
Hello Stefan,
This is an incredible tool! Thanks very much.
I tried to create the following expression with your tool but did not suceed…
Sum ({1} fact_PhysicalMovement.total_amt_std)
Regards,
Thomas
Here another try…
Sum({1<fact_PhysicalMovement.order_type={"$(=If(dim_AccountAssignmentRules.sap_account_number=14600000,'S',If(dim_AccountAssignmentRules.sap_account_number=14680000,'T',Null())))"}>}fact_PhysicalMovement.total_amt_std)
Here it is …
http://tools.qlikblog.at/SetAnalysisWizard/?sa=O8PF
Where do you struggle?
Regards
Stefan
Hello Stefan,
Thanks for your quick reply.
The resulting expression would not work when I use it in my project. The problem seemst to be related to the part between the account numbers and the else-if statements: 14600000)','$(='S')','$(=If
The tool adds e.g. a closing bracked after the account number, a $-expansion around the search string, a $-expansion around the next if statement and some extra single quotes. When I remove these, the expression works fine.
Regards,
Thomas
What exactly does not work?
Here is the result:
http://tools.qlikblog.at/SetAnalysisWizard/?sa=HJ3M
Regards
Stefan
Hallo Stefan,
cool, easy and nice to use.
bye
Konrad
Stefan,
This is a great tool which saves a lot of time. I really like the fact it gets documented immediately as this is often not the first priority and even if you’ve created the expression yourself it’s sometimes time consuming to read what it does.
So thanks for this initiative, hope QV will embed this in the QV12 release
Regards,
Arno
This is fantastic. Good stuff!
Thank you for this great tool! It will save so many people so much time and stress.
I blogged it too.
Hi Stefan,
I’ve been struggling to find out how to create a set analysis, where you would use a bookmark holding the account numbers in question and then adding a dynamic date interval for calculating the balance. I’ve been reading all over the place to find out whether this was at all possible – no luck until I came across your awesome wizard. Literally 5 clicks and my question was answered – and I even had the set analysis string that I could paste
Good job!
Best regards
Steen Olesen
Hi Stefan
This is a fantastic tool and has helped me a few times recently as i’m fairly new to QlikView Set Analysis and its syntax. Have spotted what i think is a small error in one of your examples though. Namely in Examples > Simple Modifiers > Search Expressions > example #3…
Calculate the Sum of Sales considering only records in the field Region containing the value “easter”, so this will return Sales for “Eastern”, “Western” but not for “Northern” and “Southern”.
Sum({$}Sales)
I’m not sure this would pull out Western unless i’m completely missing something!? Possible.
Like i say small issue in a great product!
Ah – it’s chopped a bit out of my pasted example above – please see the example i mention
Great tool!!!
This is a way cool tool…
I am trying to use a variable in the set analysis
START_MNT = vTargetMonth
Where the user can set the vTargetMonth
But when I can’t seem to find the syntax to get it to work. (it does not pick up any data)
START_MNT={$(=$(vTargetMonth))} is what the tool is spiting out
I have verified that the dates appear to be in the same form.
What is the trick with variables and Set analysis (or is there a better way if I did this in QV 11) I am currently working in 10 SR 3
Thanks
Hi Stefan
Es sieht sehr interessant aus. Leider konnte ich nicht ausprobieren wegen Server Problem:
Service Unavailable
HTTP Error 503. The service is unavailable.
Danke
Gruss
Hatus
Es passt!
How would you use a xor statement in a SET? I couldnt find a syntax in the tool.
sum(
if(
(
Match(Actions,10,20,30)
or Match(ActionGroup, 9000,9610)
)
and ExecutedDate >= 2010
and Status = ‘open’
, Sales
,0
)
)
Thank you
Is it possible to show some examples of how variables can be used in set analysis
When I initially commented I clicked the “Notify me when new comments are added” checkbox and
now each time a comment is added I get four emails with the same comment.
Is there any way you can remove me from that service? Appreciate it!
Hallo,
m.E. gibt es einen kleinen Bug.
laut Wizard lautet der Set Analysis Satz
Count({$}[Distinct %KeyPersonal])
richtigerweise und nur dann funktioniert es in der folgenden Logik
Count({$}Distinct [%KeyPersonal])
qlikview 11
Hi Stefan,
Wonderful concept and excellent execution. I’m sure the entire community will gain from this. Reading the comments and mapping it to the expression really helps to get a better insight into set analysis.
Thanks a lot for taking out time to do this. Much appreciated.
Regards,
Pritam
I am really thankful to the holder of this website who has shared this
enormous article at at this time.
Hi,
Nice tools.
I would like to know how to use and or with sum ?
Hi,
Could anyone share these kind of examples for set analysis with some data/excel files. So that it would be nice to understand.
Thanks,
sujeet
Wow, I’m new to QlikView and I’ve just discovered this wizard. It will save me loads of tedious work.
Well done and thank you, Merry Christmas!
Stefan,
I used your tool a few times and it has proved to be very useful; however, I just ran into a situation where the wizard did not give me the right expression. I need to set the expression to include or exclude records that have a date stored in the field. You can tell me if I am doing it wrong.
Using the Wizard, How do I add the portion to tell QlikView to exclude records where the CheckingDate is blank?
Sum({$}#MoveInCounter)
Hi Stefan,
I found what might be a couple of errors in the set analysis wizard:
1) The functions Maximum and Minimum should actually be Max and Min respectively. Maximum and Minimum produce red line errors in QV 11.
2) Also, the following set produces a red line error in QlikView:
=Max({$}receiptdate)
( I changed the Maximum to Max, but the expression itself produces a QlikView error)
Sorry, in point (2) above, the set that produces the error is:
=Max({$}receiptdate)
Hi, the link for this tool is not valid anymore
Sorry … I had to move servers … the tool is up and running again …
Regards Stefan
Please see about getting the tool back online!
Sorry … I had to move servers … the tool is up and running again …
Regards Stefan
Thanks for getting it up and running again! If interested, we have started a sub-reddit devoted to Qlikview tips and tricks! Check us out and please feel free to submit! http://www.reddit.com/r/qlikview
Stefan, Great tool!
Question would it be possible to make this portable version ? I.e. an *.exe or executable type file that still contains the same code set java, but in a Executable that we can download and run without installing when we run it. The Web version works great but in situations where I can’t get to it online this would be nice capability.
Thank you for your great work on this and Blog.
Brian
Hi,
sorry, I do absolutely not understand what you are trying to achieve …
Could you please explain …
Regards
Stefan
Basically was hoping the Wizard could be an Executable file that you could download. This would make it ‘portable’ so you don’t have to be online to use the wizard. Yeah I guess my original post was confusing.
Hi Stephan,
I experienced problems when I try to “loading set analysis expression from server….”. It will take for ever to load.
Please let me know how I can solve this issue.
Thanks,
Steve
I have just tested it with Chrome and IE 9 … which browser do you use?
Regards
Stefan
Hi Stefan,
Thanks for your reply. I am using IE 9 too. I tried one more time and still can’t load examples, which I really need.
What a beautiful tool! I really like to use it. If I can get those samples, that will be awesome!
Steve
Very Nice to Tool. Thanks Stefan.
It looks like the tool is no longer a available. Is this temporary? I hope so. Thanks.
Yep, its been removed. I too hope its restored. Thanks
It’s up and running again (my provider changed IP-addresses so the site was unfortunately down for some days).
Regards
Stefan
Seems to be giving me a different error, please confirm. Thanks
HTTP Error 500.0 – Internal Server Error
The page cannot be displayed because an internal server error has occurred.
Please restore!!!
Up and running again.
Regards
Stefan
Hi Stefan,
it seems to be a very interesset application, but unfortunately the page can not be reached
How can i get “Set Analysis Wizard for QlikView”?
Regards
Stefan
Hi Stefan,
Set Analysis Wizard is up and running again.
Regards
Stefan
have used this for ages but can’t get back in now. Has the URL changed?
i’m getting the following…
Detailed Error Information:
Module IsapiModule
Notification ExecuteRequestHandler
Handler PageHandlerFactory-ISAPI-2.0
Error Code 0×00000000
Requested URL http://tools.qlikblog.at:80/SetAnalysisWizard/Default.aspx
Physical Path E:\HostingSpaces\walthers\qliksite.com\SetAnalysisWizard\Default.aspx
Logon Method Anonymous
Logon User Anonymous
working now – thanks
Hi Paul,
yes the site was unfortunately temporarily down …
Regards
Stefan
Hi Stefan,
I am just a beginner on set analysis, and have been struggling to generate an expression for one of the charts that I have created. Here is what I need to do:
1. I have created multi box with limited text values populated (Using inline Load statement) (For ex. Brazil, India, China, Russia)
2. I have a chart where in, I have plotted year over year trend of GDP for all the countries in the data.
4. For this purpose I have stored the values selected in the multibox in a variable “vBRIC”, using vBRIC = GetFieldSelection(BRIC)
5. Now, I am trying to put a where condition in the expression of the chart so that the data is shown only for the countries selected in the multi box using the variable vBRIC.
Here is the expression I had generated:
sum({}[420_GDP per capita, PPP (constant 2005 international $)])
This is working fine as long as one selection is being made but if there are more than one selection, then nothing is displayed.
Would be great if you can help me in resolving this. Please let me know in case you need more information.
Hi Stefan,
In set analysis wizard, why I can’t pick anything in indirect set analysis (using explicit value)?
Thanks,
Steve
Hi Steve,
I do not understand, just tested it and it works:
Please have a look at those two examples:
http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=_OTI1
or
http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=_ZYP3
Hope this helps!
Regards
Stefan
Thanks Stefan for the quick response!
Great Tool…
Quick question….where we are looking to return the value of say SUM a column or simple expression. I am having trouble when I put the simple expression inside a Variable and use the variable instead.
I know the Set Analysis is correct and I my expression in my variable is correct, as i can test them all separatly, but putting it together does not return a value.
Does it support expressions as variables?
Example:
Sum( ( Set Analysis) Variable)
Assume that I have the following calc
SUM({$}[PV MTM])
I can use a variable inside the set analysis that finds me the max valuation number and I equal my Valuation to that. This works no problem. I then look for everything which is not Realised and SUM [PV MTM].
This works, no problem.
But if I wanted to sum nominal mtm if a paper trade and pv mtm if a swap trade, I could easily write a variable for that eIfMTM.
But I can’t do this…
SUM({$} eIfMTM )
or
SUM({$} $(eIfMTM ))
Infact anything I try does not return a value when a have a variable instead of an expression or field on the end.
Has anyone got something like this to work, or is it just not possible?
Thanks
Hi Stefan,
I always confused about when to use single / double quotation in set analysis; below is some example:
Sum({$}Sales)
Sum({$<Year={"}Sales)
Sum({$}Sales)
sum({1} Sales)
Is there any common rule I can follow when deciding what to use?
Thanks a lot,
Steve
Hi Stefan,
Regarding my last post regarding single/double quotation, I think I gave some wrong examples. The correct examples should be:
sum( { $ } sales)
sum( { $ } sales)
sum( { $ 2009″ } > } sales)
Thanks,
Steve
My Last Post:
I always confused about when to use single / double quotation in set analysis; below is some example:
Sum({$}Sales)
Sum({$<Year={"}Sales)
Sum({$}Sales)
sum({1} Sales)
Is there any common rule I can follow when deciding what to use?
Thanks a lot,
Steve
Stefan,
I am in consulting services, but new to QlikView. Trying to learn as fast as I can – coming from years in OBIEE.
This looks like a great tool. Thanks. I have the feeling that “one who masters set analysis, masters QlikView”
I am looking to put in the time to master this, and suggestions would be greatly appreciated.
Are there any examples that can generate E() ?
So, for example: “Show me customers that have made no purchases (Sales is the measure) in the last 3 months? ” I.E. not necessarily Sales <=0 ; just customers that have not purchased any products in the last 3 months.
Thanks,
Dave