3 Replies Latest reply: Feb 15, 2016 12:00 PM by James Peel RSS

    Combining different set analysis

    James Peel

      Hi,

       

      I've reached a point where my knowledge of set analysis has reached an end and I need help with how to solve a problem i'm having:

       

      I've created a table where the amounts per line will be summed up if the date on the line is less than the currently selected date (which I found in another query on the community). That is working great but since then I've needed to include an extra bit of analysis. I now need to only show the line if it is still marked as "Open". So I have created a flag where it equals 1 if closed and 0 if open, this is also based on date so only at a certain point will the line be marked as closed. Take the following data as an example...

       

      LineDateAmountf_Closed
      101/01/201510000
      103/05/201525000
      107/06/20155000
      110/12/201530001

       

      So in my table I want to sum up the amounts based on the date, and only show that value if the "f_Closed" is 0, based on the date I select. However currently my set analysis goes like:

       

      sum({$<[Date] = {"<=$(=max(Date))"}, f_Closed={0}>} Amount)

       

      I thought this might work at first however all this does is sum up the values apart from the one that is marked as closed, which is pretty obvious when you look at it! So i'm wondering if someone can help me so that I can achieve the results I'm after, which would effectively be:

       

      Select 01/01/2015 - shows sum of 1000

      Select 03/05/2015 - shows sum of 3500

      Select 07/06/2015 - shows sum of 4000

      Select 10/12/2015 - line no longer shows

       

      Hope that all makes sense!

       

      Many Thanks

        • Re: Combining different set analysis
          Sunny Talwar

          Select 10/12/2015 - line no longer shows

          When you select 10/12/2015, what should happen? Can you elaborate a little?

            • Re: Combining different set analysis
              James Peel

              Yeah of course. If I select any other date then I want the chart/table to sum up the values up until that point. However when we get to 10/12/2015 the record has been marked as closed, and labelled with a '1', If I were to select this date then I don't want the line to appear at all in the table rather than what I have at the minute where it just sums up the rows where the value isn't '1'.

               

              Pretty much I need to tell it that if there is a value of '1' in the column anywhere on or before the date selected, then to remove the row completely.

              • Re: Combining different set analysis
                James Peel

                Another quick question:

                 

                I've just noticed that if I select a completely unrelated date to my data (for example, 15/08/2015) then the line doesn't show. Given how the filters work in QlikView this isn't surprising as there isn't any data for that record then, however is there a way to still have the row showing regardless?

                 

                The set analysis I have only seems to work if there is actually related data for the line on the date selected