Running total not working correctly
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22471
Printed Date: 28 Apr 2024 at 5:02am
Topic: Running total not working correctly
Posted By: Nav522
Subject: Running total not working correctly
Date Posted: 04 Oct 2017 at 10:54am
Hello all -
Im trying do a running total on the amount but its not functioning correctly.
Here's my data below. I have a Grouping on (EMPID)
NAME TAXID POLICY AMT
GH
D KIM 3527 JKC74 144
D KIM 3527 JKC74 716
D MARSHALL 7896 JKC74 716
D MARSHALL 7896 JKC74 144
GF
How to achieve the Sum AMT as $860 on the Group footer?
NAME TAXID POLICY AMT
Output needed as
GF KIM 3527 JKC74 $860 860
|
Replies:
Posted By: DBlank
Date Posted: 11 Oct 2017 at 3:04am
why is marshall excluded?
how do you know to not include it from a data perspective?
|
Posted By: Nav522
Date Posted: 11 Oct 2017 at 4:12am
I think it gives either one of the NAME because of the grouping.
For me doesn't matter which NAME it returns in GF Its the DISTINCT sum for the whole Group that is what i'm looking for. Makes sense?
|
Posted By: DBlank
Date Posted: 11 Oct 2017 at 7:15am
but what field identifies it as a unique value to include in the sum once?
order on that field (inside the group) and change your RT to evaluate on change of that field.
|
Posted By: Nav522
Date Posted: 11 Oct 2017 at 7:26am
The TRANS ID
There are different amounts for each TRANS ID but the data backend returns multiple rows for the same amount.
Here it is the actual data
NAME TID TRANSID POLICY AMT
GH
D KIM 3527 DU JKC74 144
D KIM 3527 DU JKC74 144
D KIM 3527 DU JKC74 0
D MARSHALL 7896 RO JKC74 716
D MARSHALL 7896 RO JKC74 716
GF(POLICY)
I did a running total on the amount with evaluate on TRANS ID and reset on POLICY but im getting skewed SUM.
I tried a formula in evaluate AMOUNT<> Previous (AMOUNT) but still it doesn't add up
|
Posted By: DBlank
Date Posted: 11 Oct 2017 at 7:57am
maybe this?
sort on transID
set your RT to a SUM of AMT
set the evaluate for on change of field = TransId
set the reset for on each group -
place the RT in the group footer
|
Posted By: Nav522
Date Posted: 11 Oct 2017 at 8:10am
Thanks that worked. But I have a second report where I have to capture a differences of two TRANSID
i.e. SUM(DU) and SUM(RO) and show the difference between them
So basically I have to show 716 - 144 = 562
But the RT includes the duplicates and giving me 1144. ANy workaround?
|
Posted By: DBlank
Date Posted: 11 Oct 2017 at 8:17am
you know your data so not sure if this logic applies but...
create a formula and multiply the AMT by (-1) for when the rowfield="RO" then do the same RT sum but sum the formula field not the AMT field.
|
Posted By: Nav522
Date Posted: 12 Oct 2017 at 4:04am
That did it but where ever the difference between "RO" and "DU" is equal to 0. It is putting blank field instead on 0. Is there a way to suppress the whole row for that specific problem?
I tried Suppress formula on GF as DIFF = 0 but t doesn't work
|
Posted By: DBlank
Date Posted: 12 Oct 2017 at 5:02am
is it really zero or NULL?
make sure your multiplier by -1 formula is using default values for NULLs
|
Posted By: Nav522
Date Posted: 12 Oct 2017 at 7:24am
I did changed it to Default values for NULLS but still returns NULLS. So I created another formula If ISnull() then 0 and used that formula to suppress blank.
Thanks a lot
|
Posted By: Nav522
Date Posted: 18 Oct 2017 at 7:31am
Hello Dblank - I have a query about this.
The RT that I created works for only few examples
In the RT we have mentioned to Evaluate on change of TRANS ID. It works when the data is like this below
Policy TRANSID Amt
JKC74 RC 750
RC 750
DU 0
Output comes correctly as : 750
But for some data like here below it is doubling the amounts
Policy TRANSID Amt
JKC69 RC 1400
DU 0
RC 1400
DU 0
I need to get only 1400 but since the evaluate is on TRANSID its giving me 2800.
Is there any creative way to handle this situation??
[IMG]smileys/smiley5.gif" align="middle" />
|
Posted By: DBlank
Date Posted: 18 Oct 2017 at 7:48am
In example 2 each row the TransId value was changing. I think it works kind of like a next() or previous().
If you sort on policy and transid it should be fine.
|
Posted By: Nav522
Date Posted: 18 Oct 2017 at 7:54am
|
I didn't get it. Where should I include the next() previous formula? In the reset?
|
Posted By: DBlank
Date Posted: 18 Oct 2017 at 8:16am
|
Sorry. I mean that in a Running Total for the evaluate section when you use the on change of a field option it acts like the next/previous functions in a formula. It was just a way to explain why it "works" in your first example but not the second. If you change your sort order it changes the way the RT will evaluate.
|
Posted By: Nav522
Date Posted: 18 Oct 2017 at 8:52am
|
Ah got it. So by sorting it should work. Thanks
|
Posted By: Nav522
Date Posted: 30 Oct 2017 at 10:41am
Hi Dblank -
Sorry for revisiting this again. I didn't have all the data laid out so still the results are skewed.
Apart from the TRANSID field I do have PCTSPLIT Field that I needed to take into account while doing the Running Total for AMOUNT
Policy PCTSPLIT TRANSID AMT
JKC69 0 RC 0
99 DU 1400
99 DU 0
1 DU 600
With the Evaluate on TRANSID and sort on POLICY,TRANSID its still giving the wrong running total for AMT as $1400.Instead I should show $2000 as correct amount
How to include both the fields in Evaluate for RT?
Is there a formula that you can think of?
|
Posted By: DBlank
Date Posted: 31 Oct 2017 at 4:28am
|
If you cannot use the PCTSplit as the evaluate on change of field you can likely create a formula field that concatenates PCTSPLIT and TRANSID and then use that for the field for on change of.
|
Posted By: Nav522
Date Posted: 31 Oct 2017 at 5:09am
THE PCTSPLIT shows decimals as 100.00 and the TRANSID is a String.
How do I get rid of the decimals and then concatenate?
Round doesn't seem to work.
round(PCTSPLIT)+ TRANSID gives
100.00DU
100.00RC
Any idea?
|
Posted By: DBlank
Date Posted: 31 Oct 2017 at 5:19am
What data type is the PCTSPLIT field?
You can leave the decimal points in as it is not relevant to the change of the field. Note you don't have to display the field to use it in the RT.
|
Posted By: Nav522
Date Posted: 31 Oct 2017 at 5:43am
PCTSPLIT is a number, so did something like this
totext(PCTSPLIT) + TRANSID and used it in the evaluate
but i think the issue is deeper here
Based on the PCTSPLIT if its a not 100% then i have to sum up the amount which in below case should be 1500 but with concatenation of TRANSID and PCTSPLIT it is still giving me 750.
NOt sure how to tackle all these conditions
Policy PCTSPLIT TRANSID AMT
JKC69 0 RC 0
50 DU 750
50 DU 750
50 DU 0
|
Posted By: DBlank
Date Posted: 31 Oct 2017 at 5:53am
|
What I would suggest is to try to understand how you would "manually" add these up and how you would know to include and exclude rows based only on what values are available to you. Sometimes there are other fields (primary keys) in your data set that you would never think to display but are the exact value that you would want for the condition. If you can figure out the "data logic", likely someone can assist in converting that logic into a formula.
|
|