Joined: 14 Oct 2016
Location: United States
Online Status: Offline
Posts: 3
Topic: Calculating Paid Year to date total on a check? Posted: 21 Oct 2016 at 4:45am
Hello,
Crystal rookie here. I have a payment check file I have created. I need to add a formula or calculation that is able to determine the total amount paid to the given person/vendor for the year.
All of the other calculations are working (net, gross, date), but I can't seem to figure out how to structure a formula that can look at all payments to the person/vendor over the course of the year and display that total. Calendar year is fine for what I'm trying to accomplish.
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 21 Oct 2016 at 5:24am
whats your data set like and how are you grouping?
If you basic issue is that you are trying to conditionally include a row to get to a sum you have 3 main options.
1)a Formula field to flip between 0 and field value then sum the formula result e.g.
If {table.PaidDATE} in YearToDate Then {Table.PaidAMOUNT} Else 0
2) Running Total with an evaluate condition
3) Shared variables with an evaluate condition
Note options 2 and 3 only work in details and footers while option 1 will also work in a header (using the sum function)
Joined: 14 Oct 2016
Location: United States
Online Status: Offline
Posts: 3
Posted: 21 Oct 2016 at 5:53am
I think option 1 might be the way I have to go. I'm only working in the group header. My fields I'm working with are V_LASER_APCHK.CHKDT (date), V_LASER_APCHK.NET (net amount), and V_LASER_APCHK.GROSS (gross amount)
So would I be structuring like:
{V_LASER_APCHK.CHKDT} in YearToDate Then {V_LASER_APCHK.NET} Else 0
Do I need to add something to sum the output? Sorry for newbie questions.
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 24 Oct 2016 at 3:09am
Don't forget the starting 'If" in the formula.
Place the formula on the report and see the results to make sure you are getting the row level values as you want them.
Use the insert summary function as a SUM and then place it int he footers or headers where you want to see the totals.
Joined: 14 Oct 2016
Location: United States
Online Status: Offline
Posts: 3
Posted: 11 Nov 2016 at 5:03am
Late bump. Well the formula works, but it seems to be calculating year to date payments for everything (as in all payments made to all people for the year). I'm trying to narrow it down to just the year to date payments made specifically to the individual recipient (vendor) that the check is being made out to.
Am I missing something to limit/filter it for just the single vendor?
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 11 Nov 2016 at 7:13am
This really depends on what your full report data set is like and how you have it grouped or how you are determining 'the individual the check is made out to'.
Are you looking for sums at a group level and not a report level?
You can add other criteria to your formula but it seems unlikely you would need it...grouping seems more likely.
If you are running one which returns multiple 'individuals' in the same report it seems likely you would be grouping on that individual and hence also summing at that same group level.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
