Select previous value

[#1]

Hi,

I got the following code:

=SUM({<Accountnumber=, CompanyName=, CalendarDate={'$(=Minstring(CalendarDate))'}>}OpeningBalanceAmount*DebitCreditFactor)

This code gets met the value of OpeningBalanceAmount of the first date selected and multiplies it with DebitCreditFactor.

But not all dates got an OpeningBalanceAmount. Is there a way for me to select the previous OpeningBalanceAmountif no OpeningBalanceAmount is found in the selection.

Example:

CalendarDate	OpeningBalanceAmount
01-01-2013	200.00
01-02-2013	-
01-03-2013	-
01-04-2013	220

If i select date 01-03-2013 it must return the value of 01-01-2013.

Kinds regards,

Marco

[#2]

Its's better to fill the missing dates values in the script  with the previous date values. For that you have to sort the data datewise and then use PEEK function.

[Vegar]

If you want to do it in the chart try using the above function. It will do the trick.

if(sum(Balance)=0,above([Opening Balance Amount]),sum(Balance))

See attached qvw.

Best regards

Vegar Lie Arntsen

QlikView consultant at egbs consulting ab

Blog (in Swedish): bi-effekten.se

[#4]

Hi Vegar,

Thanks for your help. Nearly got it working.

Got the following code:

if(sum({<Rekeningnummer=, Bedrijfsnaam=, CalendarDate={'$(=Minstring(CalendarDate))'}>}OpeningBalanceAmount*DebitCreditFactor)=NULL(),

above([CalendarDate]),sum(OpeningBalanceAmount*DebitCreditFactor))

This only shows the total sum of the OpeningBalanceAmount. Instead of the minDate / AboveDate.

[Vegar]

You can't compare to null()  anything compared to null() is NULL. You should use the isNull() instead.

[#6]

Thansk for you reply,

if(sum({<Rekeningnummer=, Bedrijfsnaam=, OpeningBalanceDate={'$(=Minstring(OpeningBalanceDate))'}>}OpeningBalanceAmount*DebitCreditFactor)=NULL(),sum({<Rekeningnummer=, Bedrijfsnaam=, OpeningBalanceDate={'$(=Above(OpeningBalanceDate))'}>}OpeningBalanceAmount*DebitCreditFactor),sum(OpeningBalanceAmount*DebitCreditFactor))

I tried the code above but some of the account stil return with €0.00. If i use the IsNull function all fields go to '-'.