Returning 0 (Select next known value)

[#1]

Hi Community,

I am new to Qlikview and hope someone can help me out! I'm working with 2 tables. The first table is a calendertable and the second table contains my working data. The %CalenderdateId is used as connector between the tables.

The second table is build up as below:

%CalenderdateId	Opening	Transaction	Closing
01-01-2001	100
01-01-2001		25
01-01-2001		25
01-01-2001			150
05-01-2001	150
05-01-2001		10
05-01-2001			160
11-01-2001	160
11-01-2001		40
11-01-2001			200

My dashboard is working as it should be. There is only 1 problem. As soon as i select a date (like 03-01-2001)  from my calender when there is no Opening, Transaction and Closing data the system will return '-'. When the data 03-01-2001 is selected it should return the opening balance of 05-01-2001

I have tryed working with the Next and or the Below function but it seems it is returning an empty value.

Is there a code to select the next known data in the column wich can be inserted in the code below?

I'm currently useing the following codes to get the correct opening and closing amount when multiple dates are selected:

SUM({<%CalendarDateId={'$(=Minstring(%CalendarDateId))'}>}Opening)

SUM({<%CalendarDateId={'$(=Maxstring(%CalendarDateId))'}>}Closing)

Kind regards,

If there is more information needed please ask!

Mark

[hic]

If you want to associate the opening value from 05-01-2001 with other dates, you need to populate your fact table. See http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field

Further, I think it would be appropriate to store Opening and Transaction in different tables: A date can only have one opening value, but it can have several transactions.

HIC