Getting sum of column cells between two dates

[CAsba]

HI all,
I'm struggling to find a way to get the sum of the entries in a particular column starting at a date and ending at another date. Any help to rid me of total confusion would be very helpful..

[Klaus]

Sorry, not sure what you mean exactly.
Maybe you can provide a screenshot of your DG with some info?

[dunbarx]

CAsba.

Is the issue getting the column data, perhaps loaded into a variable or a field, or is it "...sum of the entries in a particular column starting at a date and ending at another date"?

Because if the second thing, I have no idea what you mean. How do you add dates together?

Or is it that the dates are somewhere else (another column?) just to identify indexes, and the data you want to add is in another column entirely?

Craig

[bobcole]

CAsba:
If I am reading your question correctly then for each row with a date field between two dates you want the sum of a different field in the row.
With that understanding try this code.

Code: Select all

on mouseUp pButtonNumber
   -- code
   put empty into field "Sum"
   
   put unixDate(field "Start Date") into tStartDate
   put unixDate(field "End Date") into tEndDate
   
   put the dgText of group "Datagrid 1" into tText
   put the number of lines in tText into tCount
   
   set the itemDelimiter to tab
   repeat with j = 1 to tCount
      put unixDate(item 1 of line j of tText) into tDate
      if tDate >= tStartDate and tDate <= tEndDate then
         add item 2 of line J of tText to tSum
      end if 
   end repeat
   
   put tSum into field "Sum"
end mouseUp

function unixDate pDate
   convert pDate to dateItems
   put item 1 of pDate into tYear
   put item 2 of pDate into tMonth
   if the length of tMonth < 2 then put 0 before tMonth 
   put item 3 of pDate into tDay
   if the length of tDay < 2 then put 0 before tDay
   put tYear & tMonth & tDay into pDate
   return pDate
end unixDate

Here is an image of my stack.

I hope this helps.
Bob

[rkriesel]

CAsba: If Bob's code gives the results you want, but you want them faster, then consider this.
Since Bob's repeat loop scans the input text twice for each line (via "line j of tText"), it's ripe for optimizing.
(For readability, I ignore the Unix date conversions.)

Code: Select all

on sumInDateRange pText, pStartDate, pEndDate
   local tSum
   repeat for each line tLine in pText
      get item 1 of tLine
      if it >= pStartDate and it <= pEndDate then
         add item 2 of tLine to tSum
      end if
   end repeat
   return tSum
end sumInDateRange

If you try both, please post what you found.
-- Dick

[CAsba]

Hi
I found a way to do what I wanted - and that was before I saw the code examples. There was some confusion about my intention, so here is my code, which hopefully explains what I was trying to achieve.

Code: Select all

[code] put the dgNumberOfLines of grp "datagrid 1" into tLines
   set the dgHilitedLines of grp "datagrid 1" to tLines
   put the dgHilitedLines of group "datagrid 1" into theLine
   put the dgDataOfLine[theLine] of group "datagrid 1" into theDataA
   put theDataA["date"]into fld "fdate"
   convert fld "fdate" from short system date to seconds
   put fld "fdate" into fDate
   
   put 0 into fld "total"
   put 0 into fld "date3"
   put fld "To2" into fld "to"
   put fld "fromm" into tDate
   
   #Set the starting row
   
   put lineoffset(tDate,the dgtext of grp "datagrid 1") into tLineIamLookingFor
   set the dghilitedlines of grp "datagrid 1" to tLineIamLookingFor
   
   put fld "fromm" into fld "date2"
   convert fld "To" from short system date to seconds
   
   convert fld "date2" from short system date to seconds
   put fld "to" into Tto
   if Tto > Fdate then
      answer "The date must not be later than the date of the last transaction. Try again"
      exit mouseup
   end if
   
   put fld "date2" into Tdate2
   put theDataA ["date"] into fld "date3"
   convert fld "date3" from short system date to seconds
   put fld "date3" into Tdate3
   
   repeat while Tdate3 <= Tto
      set the dghilitedlines of grp "datagrid 1" to (the dghilitedlines of grp "datagrid 1" + 1)
      
      put the dgHilitedLines of group "datagrid 1" into theLine
      put the dgDataOfLine[theLine] of group "datagrid 1" into theDataA
      add theDataA["payment due"]to fld "total"
      
      put theDataA ["date"] into fld "date3"
      convert fld "date3" from short system date to seconds
      put fld "date3" into Tdate3      
      
      
      if fld "date3">= fld "To" then
         answer "The total payments due at the end of the period " & field "fromm" & " to " & fld "to2" & " is £" & fld "total"
         exit mouseup
      end if
      
   end repeat

[/code]

The code examples from Dick and Bob look a bit daunting - way beyond my limited understanding of LC - but of course, I will give them a try and report back.
Thank you everyone.

[CAsba]

I tried this

Code: Select all

on sumInDateRange pText, pStartDate, pEndDate
   local tSum
   
   put the dgNumberOfLines of grp "datagrid 1" into tLines
   set the dgHilitedLines of grp "datagrid 1" to tLines
   put the dgHilitedLines of group "datagrid 1" into theLine
   put the dgDataOfLine[theLine] of group "datagrid 1" into pText
   
   put fld "Fromm" into pStartDate
   put fld "To2" into pEndDate
   
   repeat for each line tLine in pText
      get item 1 of tLine
      if it >= pStartDate and it <= pEndDate then
         add item 2 of tLine to tSum
      end if
   end repeat
   return tSum
   answer "The total is " & tSum
end sumInDateRange

but without success...

[CAsba]

I tried this

Code: Select all

on sumInDateRange pText, pStartDate, pEndDate
   local tSum
   
   put the dgNumberOfLines of grp "datagrid 1" into tLines
   set the dgHilitedLines of grp "datagrid 1" to tLines
   put the dgHilitedLines of group "datagrid 1" into theLine
   put the dgDataOfLine[theLine] of group "datagrid 1" into pText
   
   put fld "Fromm" into pStartDate
   put fld "To2" into pEndDate
   answer fld "To2"
   answer pstartdate
   answer penddate
   repeat for each line tLine in pText
      get item 1 of tLine
      if it >= pStartDate and it <= pEndDate then
         add item 3 of tLine to tSum
      end if
   end repeat
   return tSum
   answer "The total is " & tSum
   put tSum into fld "total"
   answer fld "total"
end sumInDateRange

but without success...not even the "answers" showing

[CAsba]

Hi Bob,
I tried

Code: Select all

on mouseUp pButtonNumber
   
   put empty into field "Sum"
   
   put unixDate(field "Fromm") into tStartDate
   put unixDate(field "to2") into tEndDate
   
   put the dgText of group "Datagrid 1" into tText
   put the number of lines in tText into tCount
   
   set the itemDelimiter to tab
   repeat with j = 1 to tCount
      put unixDate(item 1 of line j of tText) into tDate
      if tDate >= tStartDate and tDate <= tEndDate then
         add item 3 of line J of tText to tSum
      end if 
   end repeat
   
   put tSum into field "Sum"
end mouseUp

and it worked well - without the code for unixdate that you appended. Many thanks.I didn't know about 'repeat with' - that's a real eyeopener. Why is it working without the function unixDate pDate being coded ?

[Klaus]

Hi CAsba,

1. the RETURN keyword will leave the current handler immediately, so the next line with the ANSWER will never get executed!.
2. put the dgDataOfLine[theLine] of group "datagrid 1" into pText will put an ARRAY into pText, so you will need to loop through the keys of this array.
3. AND this is only the content of ONE array element. Your "repeat for each line..." lets me think that you are exspecting more that that (more lines), which is not the case.

You may want to:

Code: Select all

...
## put the dgDataOfLine[theLine] of group "datagrid 1" into pText
put the dgText of group "datagrid 1" into tCompleteData
put line theLine tCompleteData into pText

## Important:
set itemdel to TAB
...

But as mentioned above, this will only give you ONE line, unless your users can select multiple lines in the datagrid.
Hope that helps!

Best

Klaus

[Klaus]

You beat me by two minutes.