RESOLVED >> URGENT: View; Calculate Amount within on Date Range

HCL Domino Domino Forum
1

Hi, need your help urgently.

Working on a Client Portfolio db. Disbursed amounts & Repayments are found on two different views. Am trying to consolidate disbursed amounts within a specific date range. Please help!!! Not sure how to get around this issue. I’m hopeful i’ll find help here!

This code does the job if the value passed exists in the view, but does not calculate within a given range.

EndDate:=oirAsOnDate;

SearchCode:=oirLocation+“/”+oirMonth;

space:= " ";

month:=@Select(@Month(EndDate);“Jan”;“Feb”;“Mar”;“Apr”;“May”; “Jun”;“Jul”;“Aug”;“Sep”;“Oct”;“Nov”;“Dec”);

month2:=@Month(EndDate);

day:=@Text(@Day(EndDate));

year:=@Text(@Year(EndDate));

Odate:= day + space + month + space + year;

SearchTillDate:=@TextToTime(Odate);

getDatesLkup:= @DbLookup(“”:“NoCache”;“”:“”;“modLoanDisbursementLocation-wise”;oirLocation;4;[FailSilent]);

AmtDisb:=@Sum(@DbLookup(“”:“NoCache”;“”:“”;“modLoanDisbursementMonth-wise2”; SearchCode;6;[FailSilent]));

AmtRepaid:=@DbLookup(“”:“NoCache”;“”:“”;“modLoanRepaymentOSBalance”;oirLocation;5;[FailSilent]);

getDatesRepaid:=@DbLookup(“”:“NoCache”;“”:“”;“modLoanRepaymentOSBalance”;oirLocation;9;[FailSilent]);

dateList:=@If(oirLocation=“”;“”;getDatesRepaid);

AmtAOD := 0;

@For(n := 1; n <= @Elements(dateList); n := n + 1;

@If(dateList[n] < SearchTillDate | dateList[n] = SearchTillDate;

AmtAOD:=@If(oirMonth=“”;0; @Sum(AmtRepaid));0));

osBal:=@Abs(AmtDisb-AmtAOD);

@If(oirMonth=“”;0; osBal);

2

Subject: View; Calculate Amount within on Date Range

Suggestion => Read the FAQ

As your code is not documentment, please explain to us what you’re trying to do. Otherwise, I (and others) may make the wrong assumptions?

For example, what do you mean “This code does the job if the value passed exists in the view, but does not calculate within a given range.”

What are you passing, how are you passing. What date range??

I see you have a line like:

Odate:= day + space + month + space + year;

SearchTillDate:=@TextToTime(Odate);

I believe this will only work if someone has their regional settings set to this format, which may not be true.

3

Subject: URGENT: View; Calculate Amount within on Date Range

Hi, thx a ton for your response. I have a view & the keyword (1st col) is Location+“/”+Month_Year. What I need is to generate an As On Date report that would give me values as on the selected Month. The View has one column that has Disbursed Amounts that I need to consolidate in the report.

Eg.: Period of loans disbursed is between April 2004 - March 2009. If I choose a Location & month Jan 2008, the report should show value of loans disbursed till Jan 2008 only. Likewise if I choose month March 2008, the report should show values of loans disbursed till March 2008 only & not include amount beyond the chosen month.

Hope I was clear in what I’m try to explain. The code that I put up works best only when the KEYWORD exists in the view (as normally would using @Dblookup!).

Any help is appreciated. I prefer not to use folders as many users would be accessing the database. But if that is the best option, I would like some guidance in that direction too.

Thank you once again in advance.

Isaac

4

Subject: RESOLVED: URGENT: View; Calculate Amount within on Date Range

Hi guys, thx a ton for all the interest. I’ve solved this puzzle by this code [below]. I have used a combination of Location & DocumentUniqueID & used an @DBlookup to retrieve values within a given date range.

If there is a better, logical & simpler way to do this can I request for your advice on this please.

Thank you once again.

Isaac

CODE I’ve used (changed variable names):

DisbList:= @If(oirLocation=“”;“”;@DbLookup(“”:“NoCache”;@DbName;“modLoanDisbursementLocationUID2”;oirLocation;12));

DisbSum:=0;

TotDisbSum:=0;

@If(oirLocation=“” | oirMonth=“”;0;

@For(n := 1; n <= @Elements(DisbList); n := n + 1;

key := @If(oirLocation="";"";oirLocation+"/"+DisbList[n]);

getDisbDate:=@DbLookup( "":"NoCache" ; "" : ""; "modLoanDisbursementLocationUID1" ; key ; 5 );

getDisbAmt:=@DbLookup( "":"NoCache" ; "" : ""; "modLoanDisbursementLocationUID1" ; key ; 6 );



DisbSum:=@If(getDisbDate < oirAsOnDate; getDisbAmt;0);

TotDisbSum := TotDisbSum+DisbSum;

TotDisbSum));

TotDisbSum;