7 Replies Latest reply: Jan 31, 2013 7:05 AM by darthsyd RSS

    Using nested functions within Set Analysis

      Hi Everyone,


      This might seem simple, but I am quite new to Qlikview.


      I have sales data loaded into qlikview on a weekly basis. I.e, every week data is loaded with sales of various clients for that week. I currently have a selection box that lets users select a date and a table that shows sales (and some other information) for each client.


      I would like to now display a Year to date number for each client, based on the current selection. Assuming the user clicks on 15/12/2012, then I want a sum of all weeks of sales upto that date. That is, sum ALL weeks (or rows) that are LESSER THAN OR EQUAL TO the currently selected date.


      My reasoning was I would need to use a set analysis that uses current selection AND a date comparing function.


      My date field that the user selects is called SelectDate and my Sales field is called Sales. I only have 2012 data loaded.


      This is what I came up, and it is miserably failing!


      Sum ( {1 <SelectDate<={GetFieldSelections(SelectDate)}> } Sales)


      Im sure i am tripping up with the bit in red i,e using <= for lesser than or equal to. But even if i remove the "<", my expression does not work.i.e


      Sum ( {1 <SelectDate={GetFieldSelections(SelectDate)}> } Sales).. Which I assumed should simply sum all data based on current selection.


      Any help will be much appreciated!

        • Re: Using nested functions within Set Analysis
          Roland Vecera



          Quite some ways to achive this:


          =sum({$<SelectDate={">=$(=yearstart(SelectDate))<=$(=only(SelectDate))"}>} Sales)


          $... respect the current user selection

          then overwrite SelectDate with a dynamic expression similar to:



          This tool can help with Set Analysis: http://tools.qlikblog.at/SetAnalysisWizard/

            • Re: Using nested functions within Set Analysis

              Hi rva_heldendaten,


              Thank you so much for your reply.


              I did paste your formula into the expression and it seems to work!! Ill have to recheck the numbers as I have a lot of data. (Atleast it produces an output, much better than mine..)


              Unfortunately though, I am bamboozeled by the logic of the formula. I would be grateful if you could explain it to me so that I can design similar ones in the future.


              Here are some of my questions:


              1. I assume yearstart() gives the start date of the current year. I.e if the user selects 18/12/2012, yearstart() returns 01/01/2012


              2. I am not sure what the only() funtiuon does, but I assume in this case that it returns 18/12/2012


              3. What really confuses me, is the $ sign at the start of the formulas. Does that not reduce the formulas to work only on the current selection or rows of data?


              Let me explain how I understand the logic, and if you can correct my flaw, it would be great.


              Let's assume my universal data set (which captures sales on a weekly basis) is:



              SelectDate    Customer Sales

              04/12/2012 A 100

              04/12/2012 B 200

              04/12/2012 C 300

              11/12/2012 A 400

              11/12/2012 B 500

              11/12/2012 C 600

              18/12/2012 A 700

              18/12/2012 B 800

              18/12/2012 C 900

              25/12/2012 A 1000

              25/12/2012 B 1100

              25/12/2012 C 1200


              Now, if the user selects 18/12/2012 in the selection, my understanding is that yearstart() returns 1/1/2012 and only() returns 18/12/2012. BUT what confuses is me is that if the formula begins with a $, i.e "=sum({$<SelectDate", Wont the data set that the formulaes then work on be reduced to just the current selection, i.e


              18/12/2012 A 700

              18/12/2012 B 800

              18/12/2012 C 900


              Which will then return sales of 2400 instead of the correct answer of 4500


              Would be great if you could point out the flaw in my logic!


              Thank you!

                • Re: Using nested functions within Set Analysis
                  Roland Vecera

                  $ respects the current User selection.


                  So if a user selects "Customer A", my expression will only return YTD for this Customer.

                  --> my expression says: Respect all Userselections, but overwrite the date selection with =>1/1/2012<=18/12/2012



                  If you use "1" in set analysis, qlikview will ignore the user's "Customer A" selection, and will return all Sales between "=>1/1/2012<=18/12/2012"


                  I would say "$" is more often used than "1"ö, as typically the user has done selection in various fields (article, customer, country) and you only want to overwrite the timeselection..