4 Replies Latest reply: May 25, 2016 8:32 AM by Wendy Harrison RSS

    Sum a selection of results in a Column.

    Wendy Harrison

      Hi,

       

      I'm New to Qlik View and have just completed my training.

       

      Just wondering if anyone can help please.

       

      I have a column of data which contains results from One to Five.

       

      I need to sum only the results which come in for 4 and 5 and ignore the 1,2 and 3's.

       

      I am sure there is a simple way to do this but I am really struggling to get the right results.

       

      I would appreciate it if anyone could point me in the right direction.

       

      Many Thanks

        • Re: Sum a selection of results in a Column.
          Sunny Talwar

          Lets say you data looks like this:

           

          Field1, Field2

          1, 20

          3, 20

          4, 10

          3, 20

          5, 40

          2, 10

          3, 40

          4, 10

           

          Now if you sum Field2 where Field1 = 4 or 5, you can do this:

           

          Sum({<Field1 = {4, 5}>} Field2)

           

          In here I am using set analysis to tell the expression to sum only places where Field1 = 4 or Field = 5. If in case you want this to change when you make selections in Field1, then you can further add an *

           

          Sum({<Field1 *= {4, 5}>} Field2)

           

          Here it will show 4 or 5's sum, but as soon as your select 1 or 2 or 3 in Field 1, the sum will become 0.

           

          There are a lot of possibilities with set analysis, we just need to know what exactly your end goal is.

           

          Best,

          Sunny

          • Re: Sum a selection of results in a Column.
            Wendy Harrison

            Hi Sunny,

             

            Thank you for your reply.

             

            I have got confused and I should have been doing a Count instead of a sum. For example count all the 4's and 5's and combine them together. 

             

            In your example there are 2 x 4 and 1 x 5 so the total should be 3 overall.

             

            I have created a bar chart and I am able to choose 4 & 5 count results but although I am able to get an overall total for the two, the bar chart still splits it into 2 x 4 & 1 x 5 stacked.  I need to amalgamate the totals on the bar chart so that the 4 & 5 counts are totalled as 3 and appear as one colour and when drilling down stay combined.

             

            Would you be able to suggest a way to get a Bar chart which combines the two counts as a total.

             

            I have tried a lot of combinations but just cant get them to total together.  I hope this makes sense.

             

            Once again Thank you for your help on this.

             

            Kind Regards

            Wendy

            • Re: Sum a selection of results in a Column.
              Wendy Harrison

              Thanks Sunny,

               

              That works

               

              Kind Regards

              Wendy