topic Re: Select previous value in QlikView

Select previous value

Tue, 25 Jun 2013 07:27:38 GMT

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

Re: Select previous value

Tue, 25 Jun 2013 07:31:17 GMT

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.

Re: Select previous value

Vegar — Tue, 25 Jun 2013 09:00:12 GMT

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

Re: Select previous value

Tue, 25 Jun 2013 09:44:51 GMT

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.

Re: Select previous value

Vegar — Tue, 25 Jun 2013 13:58:54 GMT

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

Re: Select previous value

Tue, 25 Jun 2013 14:45:44 GMT

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 '-'.