Set Analysis Wizard for QlikView

By Stefan Walther 25 Comments
This entry was posted in Creating Applications, QlikView-Online-Tools

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:

Open the Set Analysis Wizard for QlikView

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.

Possibly related posts:

Published: December 3, 2011
and tagged Expression, Formula, Generator, Set Analysis, Set Analysis Wizard, Wizard. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

25 Comments

Ralph J.

Posted December 3, 2011 at 00:08 | Permalink

Wow! Good stuff Stefan! Thanks!

Reply
Vladimir Pilonka

Posted December 3, 2011 at 03:20 | Permalink

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.

Reply
- Stefan Walther
  
  Posted December 6, 2011 at 18:21 | Permalink
  
  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
  
  Reply
Matan

Posted December 3, 2011 at 17:32 | Permalink

Look like a wandefull tool that can be very usefull and timesaving.
Thanks a lot !!

Reply
Brian Garland

Posted December 3, 2011 at 17:40 | Permalink

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.

Reply
Angie Paulens

Posted December 3, 2011 at 19:02 | Permalink

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

Reply
Ebony Stutesman

Posted December 3, 2011 at 20:22 | Permalink

Nice one!
Would be nice if you could also publish a QV-file with all the expressions used in your examples!

Greetings, Ebony

Reply
- Stefan Walther
  
  Posted December 6, 2011 at 18:25 | Permalink
  
  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
  
  Reply
Daan Koster

Posted December 3, 2011 at 20:58 | Permalink

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

Reply
- Stefan Walther
  
  Posted December 6, 2011 at 18:27 | Permalink
  
  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
  
  Reply
Stephen Redmond

Posted December 5, 2011 at 10:34 | Permalink

Hi Stefan,

Very good! Nice tool for people who don’t do a lot of set analysis.

Regards,

Stephen

Reply
Ralph G.

Posted December 5, 2011 at 12:13 | Permalink

This is awesome, as always! Thank you very much for sharing this with the world!

Reply
Ashwin Pagare

Posted December 6, 2011 at 06:57 | Permalink

Hello Stefan,

Thanks for the Awesome work done for developers new to QV world to know Set Analysis.

Regards
Ashwin Pagare

Reply
Ed Bobrin

Posted December 9, 2011 at 12:23 | Permalink

Stefan,

Nice job! I am sure this will get used by thousands of people….

Ed

Reply
- Stefan Walther
  
  Posted December 13, 2011 at 22:56 | Permalink
  
  Let’s see
  
  Reply
Anil

Posted December 17, 2011 at 08:31 | Permalink

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

Reply
Thomas Staffelbach

Posted December 23, 2011 at 08:01 | Permalink

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

Reply
- Thomas Staffelbach
  
  Posted December 23, 2011 at 08:08 | Permalink
  
  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)
  
  Reply
  - Stefan Walther
    
    Posted December 23, 2011 at 09:43 | Permalink
    
    Here it is …
    
    http://tools.qlikblog.at/SetAnalysisWizard/?sa=O8PF
    
    Where do you struggle?
    
    Regards
    Stefan
    
    Reply
    - Thomas Staffelbach
      
      Posted December 23, 2011 at 11:09 | Permalink
      
      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
      
      Reply
- Stefan Walther
  
  Posted December 23, 2011 at 09:41 | Permalink
  
  What exactly does not work?
  
  Here is the result:
  http://tools.qlikblog.at/SetAnalysisWizard/?sa=HJ3M
  
  Regards
  Stefan
  
  Reply
Konrad

Posted December 31, 2011 at 11:12 | Permalink

Hallo Stefan,

cool, easy and nice to use.

bye
Konrad

Reply
Arno

Posted January 4, 2012 at 12:13 | Permalink

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

Reply
Rubun

Posted January 5, 2012 at 01:11 | Permalink

This is fantastic. Good stuff!

Reply
Chris

Posted February 21, 2012 at 10:41 | Permalink

Thank you for this great tool! It will save so many people so much time and stress.
I blogged it too.

Reply