Set Analysis Wizard for QlikView

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:

  1. 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)
  2. 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"

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.
Bookmark and Share

and tagged , , , , , . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

77 Comments

  1. Ralph J.
    Posted December 3, 2011 at 00:08 | Permalink

    Wow! Good stuff Stefan! Thanks!

  2. 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.

    • 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

      • David
        Posted February 4, 2014 at 16:56 | Permalink

        Would it be possible to add a reverse lookup for expressions?

        • David
          Posted February 4, 2014 at 16:57 | Permalink

          BTW, your tool is excellent and should be incorporated into the Qlik itself.

  3. Matan
    Posted December 3, 2011 at 17:32 | Permalink

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

  4. 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.

  5. 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

  6. 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

    • 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

  7. 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

    • 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

  8. 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

  9. 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!

  10. 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

  11. Posted December 9, 2011 at 12:23 | Permalink

    Stefan,

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

    Ed

  12. 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

  13. 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

    • 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)

      • Posted December 23, 2011 at 09:43 | Permalink

        Here it is …

        http://tools.qlikblog.at/SetAnalysisWizard/?sa=O8PF

        Where do you struggle?

        Regards
        Stefan

        • 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

    • 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

  14. Konrad
    Posted December 31, 2011 at 11:12 | Permalink

    Hallo Stefan,

    cool, easy and nice to use.

    bye
    Konrad

  15. 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

  16. Rubun
    Posted January 5, 2012 at 01:11 | Permalink

    This is fantastic. Good stuff!

  17. 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.

  18. Posted March 6, 2012 at 08:14 | Permalink

    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

  19. Paul
    Posted March 12, 2012 at 17:07 | Permalink

    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!

  20. Paul
    Posted March 12, 2012 at 17:09 | Permalink

    Ah – it’s chopped a bit out of my pasted example above – please see the example i mention

  21. Claudio
    Posted March 23, 2012 at 11:30 | Permalink

    Great tool!!!

  22. Nechama
    Posted May 10, 2012 at 21:17 | Permalink

    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

  23. Hatus Peters
    Posted June 14, 2012 at 11:57 | Permalink

    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

  24. Hatus
    Posted June 21, 2012 at 14:19 | Permalink

    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

  25. Paul
    Posted July 2, 2012 at 11:12 | Permalink

    Is it possible to show some examples of how variables can be used in set analysis

  26. Posted September 29, 2012 at 13:55 | Permalink

    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!

  27. Posted October 17, 2012 at 13:32 | Permalink

    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

  28. Pritam
    Posted November 6, 2012 at 17:03 | Permalink

    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

  29. Posted November 8, 2012 at 11:42 | Permalink

    I am really thankful to the holder of this website who has shared this
    enormous article at at this time.

  30. Tushar
    Posted November 23, 2012 at 06:02 | Permalink

    Hi,

    Nice tools.

    I would like to know how to use and or with sum ?

  31. Sujeet
    Posted December 5, 2012 at 16:51 | Permalink

    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

  32. Jim
    Posted December 17, 2012 at 21:01 | Permalink

    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!

  33. Miguel Gavidia
    Posted February 6, 2013 at 21:12 | Permalink

    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)

  34. Kevin P.
    Posted February 27, 2013 at 19:29 | Permalink

    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)

  35. Kevin P.
    Posted February 27, 2013 at 19:48 | Permalink

    Sorry, in point (2) above, the set that produces the error is:
    =Max({$}receiptdate)

  36. Robson
    Posted April 3, 2013 at 13:17 | Permalink

    Hi, the link for this tool is not valid anymore

    • Posted April 4, 2013 at 10:49 | Permalink

      Sorry … I had to move servers … the tool is up and running again …

      Regards Stefan

  37. Andrew L
    Posted April 3, 2013 at 18:25 | Permalink

    Please see about getting the tool back online!

    • Posted April 4, 2013 at 10:17 | Permalink

      Sorry … I had to move servers … the tool is up and running again …

      Regards Stefan

  38. Brian G
    Posted April 9, 2013 at 20:09 | Permalink

    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

    • Posted April 9, 2013 at 22:37 | Permalink

      Hi,

      sorry, I do absolutely not understand what you are trying to achieve …
      Could you please explain …

      Regards
      Stefan

      • Brian G
        Posted April 9, 2013 at 22:59 | Permalink

        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.

  39. Steve Zheng
    Posted November 18, 2013 at 22:35 | Permalink

    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

    • Posted November 18, 2013 at 23:35 | Permalink

      I have just tested it with Chrome and IE 9 … which browser do you use?

      Regards
      Stefan

      • Steve Zheng
        Posted January 8, 2014 at 17:59 | Permalink

        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

  40. Viswa
    Posted December 20, 2013 at 11:03 | Permalink

    Very Nice to Tool. Thanks Stefan.

  41. Jaydon
    Posted January 2, 2014 at 21:58 | Permalink

    It looks like the tool is no longer a available. Is this temporary? I hope so. Thanks.

    • Brian G
      Posted January 3, 2014 at 02:06 | Permalink

      Yep, its been removed. I too hope its restored. Thanks

      • Posted January 6, 2014 at 22:21 | Permalink

        It’s up and running again (my provider changed IP-addresses so the site was unfortunately down for some days).

        Regards
        Stefan

        • Brian G
          Posted January 7, 2014 at 15:15 | Permalink

          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.

  42. Jason
    Posted January 3, 2014 at 20:31 | Permalink

    Please restore!!!

  43. Stefan
    Posted January 4, 2014 at 14:58 | Permalink

    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

  44. Paul
    Posted January 7, 2014 at 15:24 | Permalink

    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

    • Paul
      Posted January 8, 2014 at 10:27 | Permalink

      working now – thanks

      • Posted January 10, 2014 at 17:56 | Permalink

        Hi Paul,

        yes the site was unfortunately temporarily down …

        Regards
        Stefan

  45. Ashish Tyagi
    Posted January 9, 2014 at 18:39 | Permalink

    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.

  46. Steve Zheng
    Posted January 10, 2014 at 17:02 | Permalink

    Hi Stefan,
    In set analysis wizard, why I can’t pick anything in indirect set analysis (using explicit value)?

    Thanks,
    Steve

  47. Mark Smith
    Posted January 11, 2014 at 05:35 | Permalink

    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)

    • Mark Smith
      Posted January 12, 2014 at 14:12 | Permalink

      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

  48. Steve Zheng
    Posted January 24, 2014 at 21:24 | Permalink

    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

  49. Steve Zheng
    Posted January 27, 2014 at 14:58 | Permalink

    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

  50. David Dumas
    Posted March 16, 2014 at 00:21 | Permalink

    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

Post a Comment

(required)
(required)

Your email is never published nor shared.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Subscribe without commenting