5 Replies Latest reply: Jun 26, 2012 10:21 AM by Stefan Wühl RSS

    Subtracting in Set Analysis...???

      Hi,

       

       

      Can someone tell me what's wrong with this syntax. I am trying to calculate a difference two values in set analysis?

       

      Sum({$<Year=(Year + {2012,2005})-{1999}>}Amount)   to get total amount for the current selected year and sum of amount for 2005,2012 minus the sum of amount for the year 1999.

       

      I found a similar syntax in QlikView help, but it does not help much either..

       

       

      Regards and Thanks,

       

      -Khaled

        • Re: Subtracting in Set Analysis...???
          Stefan Wühl

          The expression looks ok to me at first glance. Are your Year and Amount fields located in the same table?

           

          Could you post a small sample together with your expected result?

            • Re: Subtracting in Set Analysis...???

              Hi,

               

              Yes, the fields are from the same table. Here's the sample file for better insight.

              Also, what would be my approach for the same if fields were from different tables?

               

              Regards,

               

              -Khaled.

                • Re: Subtracting in Set Analysis...???
                  Stefan Wühl

                  Khaled if you want to subtract sum of amount for certain years, I think you should just use

                   

                  =sum({<Year = {2010,2011}>} Amount) - sum({<Year = {1999}>} Amount)

                   

                  If you use something like

                   

                  =sum(

                  {

                  <Year={2010,2011}-{1999}>

                  }

                  Amount)

                   

                  This is not calculating the sum of amount for years 2010 and 2011 minus the sum of amount for year 1999, as you noticed. It is defining a selection in field Year, setting years  2010 and 2011 and excluding 1999 (but this is never part of 2010, 2011 anyway). So you will always only get the sum of amount for the (in the set expression) set years 2010 and  2011, i.e. 1500.

                   

                  I am sorry, I haven't spent enough attendance to your initial requirement ("to get total amount for the current selected year and sum of amount for 2005,2012 minus the sum of amount for the year 1999."): you might get the correct result with your original expression (including the current selection) only if the excluded year is part of that current selection (this is what also the sample in the HELP says).

                   

                  Hope this helps,

                  Stefan

                    • Re: Subtracting in Set Analysis...???

                      Thanks Stefan, I am using =sum({<Year = {2010,2011}>} Amount) - sum({<Year = {1999}>} Amount) already but had it (sum({<Year={2010,2011}-{1999}>}Amount) ) worked, I think the calculations would have been much lighter than what I otherwise have using the original expression since I have used similar calculations in multiple instances.

                       

                      Regards,

                       

                      -Khaled.

                        • Re: Subtracting in Set Analysis...???
                          Stefan Wühl

                          Khaled,

                           

                          I just don't think that you can create a set that does what you want: subtracting values.

                          In a set expression field modifier you define which field values are used to filter your results, but it's just selected / not selected.

                          And results for non-selected field values will just not be taken into account (i.e. not added up, but also not subtracted).

                           

                          If it's all about achieving a higher maintainability, I think you could look into using variables.

                           

                          Regards,

                          Stefan