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

          Hi!

           

          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:

          SelectDate={">=01/01/2003<=10/01/2013"}

           

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