| Author |
Message |
ChrisG
Newbie
Joined: 12 Mar 2013
Online Status: Offline
Posts: 20
|
  
Topic: Formula
Posted: 13 Mar 2013 at 10:08pm |
|
Hi Guys.
Need help with a formula for counting transactions in a monthly period for customer statements. I have 4 fields at the bottom of the report which would be; what the current customer owes for that month, then another field what they owe for the previous month and then another field for the next previous month. Have hit a brick wall now and would appreciate the help.
Cheers
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 14 Mar 2013 at 3:54am |
Not sure I understand your question. Do you mean that you need these 4 summary fields to be created?
Yiou can use runningtotals with a different evaluation formula for each one
examples
name=currentmonth
fiekld to summarize=amount owed field
type=sum
evaluate=use a formula
table.date in monthtodate
reset= on change of group (if you are grouping customer)
place in group footer, RTs do not work in headers
other evalaute formulas
//lastmonth
table.date in lastfullmonth
//2monthsago
table.date in dateserial(year(currentdate,month(currentdate)-2,1) to
dateserial(year(currentdate,month(currentdate)-1,1-1)
//3mothsago
table.date in dateserial(year(currentdate,month(currentdate)-3,1) to dateserial(year(currentdate,month(currentdate)-2,1-1)
|
| IP Logged |
|
ChrisG
Newbie
Joined: 12 Mar 2013
Online Status: Offline
Posts: 20
|
Posted: 14 Mar 2013 at 4:31am |
|
That's great DB. Was trying to use the running total but it just wasn't turning out the figures that I wanted.
One last question though, how would I collect all the amounts that are older than 4 months?
Thanks for your help
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 14 Mar 2013 at 4:33am |
//4months or more
table.date < dateserial(year(currentdate,month(currentdate)-3,1)
|
| IP Logged |
|
ChrisG
Newbie
Joined: 12 Mar 2013
Online Status: Offline
Posts: 20
|
Posted: 14 Mar 2013 at 4:38am |
|
Beautiful DB.
Thanks for the help!
|
| IP Logged |
|
ChrisG
Newbie
Joined: 12 Mar 2013
Online Status: Offline
Posts: 20
|
Posted: 14 Mar 2013 at 4:53am |
|
Getting the error message of ' The ) is missing' is the latter formulas. Have added the bracket it and getting too many arguments for the function.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 14 Mar 2013 at 4:56am |
Sorry, missed the closing parenth on the year(currentdate) parts
//lastmonth
table.date in lastfullmonth
//2monthsago
table.date in dateserial(year(currentdate),month(currentdate)-2,1) to
dateserial(year(currentdate),month(currentdate)-1,1-1)
//3mothsago
table.date in dateserial(year(currentdate),month(currentdate)-3,1) to dateserial(year(currentdate),month(currentdate)-2,1-1) Edited by DBlank - 14 Mar 2013 at 4:56am
|
| IP Logged |
|
ChrisG
Newbie
Joined: 12 Mar 2013
Online Status: Offline
Posts: 20
|
Posted: 14 Mar 2013 at 4:59am |
|
Thanks mate.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 14 Mar 2013 at 5:02am |
you might want to place these on the detail section to see the values change (or not change) per row. It is a good way to validate (or debug) that they are doing what you expect/need.
Once you like what you see delete them from the detail section as needed.
|
| IP Logged |
|
|
|
