Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
Line | Date | Amount | f_Closed |
---|---|---|---|
1 | 01/01/2015 | 1000 | 0 |
1 | 03/05/2015 | 2500 | 0 |
1 | 07/06/2015 | 500 | 0 |
1 | 10/12/2015 | 3000 | 1 |
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
Select 10/12/2015 - line no longer shows
When you select 10/12/2015, what should happen? Can you elaborate a little?
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.
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