Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
QlikView consultant at egbs consulting ab
Blog (in Swedish): bi-effekten.se
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.
You can't compare to null() anything compared to null() is NULL. You should use the isNull() instead.
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 '-'.