Hello All,
I have view which has 4 columns
Revenue Date-------Duration-------Value----Percentage
Jan/10-----------------2----------------1000—10%
Sep/10----------------4---------------5000—20%
I want to generate monthly revenue based on Duration/Value*Percentage, and display in new view like
For Example:
1st document contains date jan/10 with 2 months duration. So Revenue start from jan ends feb/10(2 months duration from jan, that will Jan and Feb).
2nd doc, revenue starts from Sep/10 ends with Dec/10 like that
So revenue per month for the 1st document would be
it should display in new view like
Percentage----Jan—Feb----Mar—like till—Dec
10%------------50----50
Percentage-----Jan—Feb----Sep–Oct–Nov–Dec
20%---------------------------250----250-250-250
Till here i don’t have any issue i am able to get it, monthly wise values based my script, but the problem here is i need to get all values which belongs to 10% in one row, 20% to one row so there should be only 2 rows in my new view. I am not able to find out how to achieve this. Pls some one let me know what is the problem in my code.
Based on my code in new view i am getting docs like this
Date----Final value----Jan–Feb–Mar—Apr–Sep–Oct–till Dec
01/10----50------------50
02/10-----50-----------------50
09/10------250----------------------------------250
10/10------250----------------------------------------250
11/10------250-----------------------------------------------
12/10------250--------------------------------------------------250
But i need to display like this:
Perc—Final value----Jan–Feb–Mar—Apr–Sep-Oct-Dec
10%—50-------------50----50----in single row(All 10% docs)
20%—250-------------------------------------250–250–250
If we add new doc that value should go to respect row(based on Percentage)
Date Column formula:
@Text(@Year(Date))+“/”+@Text(@Month(Date))
Final Value Formula:
@Round(Perc)
Each Month has formula like :
Fcolumn:=Text(@Year(Date))+“/”+@Text(@Month(Date));
@If(Fcolumn=“01/10”;@Round(Perc);@Return(“”))
But here i am not getting Percentage for each document
Code:
Dim sess As NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim tempdoc As NotesDocument
Dim tfdoc As NotesDocument
Dim doccoll As NotesDocumentCollection
Dim view As NotesView
Dim tempview As NotesView
Dim tempvc As NotesViewEntryCollection
Dim i As Integer, j As Integer, ival As Long
Dim cmp1 As String, cmp2 As String
Dim incdateval As NotesDateTime
Dim dtitem As NotesItem
Dim prtg As NotesItem
On Error GoTo oops
Set sess = New NotesSession
Set db = sess.Currentdatabase
Set view = db.Getview(“Temp”)
Set doc = view.Getfirstdocument()
Set tempview = db.Getview(“Forecast”)
Set tempvc = tempview.Allentries
Set dtitem = doc.Getfirstitem(“Date”)
Set incdateval = dtitem.Datetimevalue
Call incdateval.Adjustmonth(-1)
While Not doc Is Nothing
Set tempvc = tempview.Allentries
If tempvc.Count = 0 Then
For i=1 To CInt(doc.Duration(0))
Set tempdoc = db.Createdocument()
tempdoc.form = “Forecast”
tempdoc.Perc = (CLng(doc.Value(0))/ CLng(doc.Duration(0)))* doc.Percent(0)
Call incdateval.Adjustmonth(1)
tempdoc.Date = CDat(incdateval.Dateonly)
Call tempdoc.Save(True,False)
Call tempview.Refresh()
Next
Else
Set dtitem = doc.Getfirstitem(“Date”)
Set incdateval = dtitem.Datetimevalue
Call incdateval.Adjustmonth(-1)
For i=1 To CInt(doc.Duration(0))
Call incdateval.Adjustmonth(1)
cmp1 = CStr(Year(incdateval.DateOnly))+“-”+CStr(Month(incdateval.DateOnly))
Set doccoll = tempview.Getalldocumentsbykey(cmp1,False)
Set tfdoc = doccoll.Getfirstdocument()
If Not tfdoc Is Nothing Then
tfdoc.Perc = CLng(tfdoc.Perc(0)) + (CLng(doc.Value(0))/ CLng(doc.duration(0)))* doc.Percent(0)
Call tfdoc.Save(True,False)
Else
Set tempdoc = db.Createdocument()
tempdoc.form = “Forecast”
tempdoc.Perc = (CLng(doc.Value(0))/ CLng(doc.Duration(0)))* doc.Percent(0)
tempdoc.Date = CDat(incdateval.Dateonly)
tempdoc.perct= doc.Percent(0) * 1
Call tempdoc.Save(True,False)
End If
Next
End If
Set doc = view.Getnextdocument(doc)
Wend
oops:
MsgBox "Error occured at line # : "+CStr(Erl)+Chr(10)+"Error is : "+Error$,64,“Error Info”
Exit Sub