Print Page | Close Window

Percentage using distinct count and running total

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=22317
Printed Date: 27 Apr 2024 at 10:23pm


Topic: Percentage using distinct count and running total
Posted By: fuller31
Subject: Percentage using distinct count and running total
Date Posted: 02 Jun 2017 at 6:24am
I have a report where I need to take #CANCL (which is a running total), multiply it by 100, then divide by DistinctCount of cdcall.number (trying to get a percentage). I have been trying for hours to figure this out but cannot. I have the values for those two on the report, so no problems there. I just can't figure out what type of formula I need, or how to get those fields to get in a formula and do the math I need. I have attached an image since I probably sound like a crazy person. Any help would be greatly appreciated.

https://www.dropbox.com/s/yearidruvcd5h3e/CR.PNG - https://www.dropbox.com/s/yearidruvcd5h3e/CR.PNG


Replies:
Posted By: DBlank
Date Posted: 02 Jun 2017 at 7:11am
{#CANCL}%DistinctCount ({cdcall.number})

This should be used in a formula field.
the formula field can only be placed in the report footer as the RT will only have the final sum for #CANCL in that location

You likely also want to handle this with an if statement in case you denominator is 0


if DistinctCount ({cdcall.number}) = 0 then 0 else {#CANCL}%DistinctCount ({cdcall.number})

Posted By: fuller31
Date Posted: 02 Jun 2017 at 7:14am
Originally posted by DBlank

{#CANCL}%DistinctCount ({cdcall.number})

This should be used in a formula field.
the formula field can only be placed in the report footer as the RT will only have the final sum for #CANCL in that location

You likely also want to handle this with an if statement in case you denominator is 0


if DistinctCount ({cdcall.number}) = 0 then 0 else {#CANCL}%DistinctCount ({cdcall.number})

I see. The only issue is, I have groups, and I wanted that for each group. But I understand what you're saying about it having to be in the report footer. I think this is going to be a situation where I'm just not going to be able to do what I want because of how I had to come up with the other values.

Posted By: DBlank
Date Posted: 02 Jun 2017 at 7:16am
If your Rt is reset on each group you can place this in the group footer.
If your DistinctCount ({cdcall.number}) needs to be per group you need to add a group condition into that

DistinctCount ({cdcall.number},{table.groupfield})

Posted By: fuller31
Date Posted: 02 Jun 2017 at 7:32am
Originally posted by DBlank

If your Rt is reset on each group you can place this in the group footer.
If your DistinctCount ({cdcall.number}) needs to be per group you need to add a group condition into that

DistinctCount ({cdcall.number},{table.groupfield})

The RT is reset on each group. I placed {#CANCL}%DistinctCount ({cdcall.number}) in the group footer, and I'm getting a number; it's just the wrong number. For example, if there were 14 calls (derived from DistinctCount of cdcall.number) and 4 cancellations (derived from #CANCL, which is a running total), that should come out to around 28%. It is giving me 5.

Posted By: DBlank
Date Posted: 02 Jun 2017 at 7:35am
place both the fields (the Running total and the distinct count summary field) into the group footer and verify that each are giving you , per group, the numbers you expect.

Posted By: fuller31
Date Posted: 02 Jun 2017 at 7:44am
Originally posted by DBlank

place both the fields (the Running total and the distinct count summary field) into the group footer and verify that each are giving you , per group, the numbers you expect.

Thats where they were, and yes, they are giving the correct numbers.

Posted By: DBlank
Date Posted: 02 Jun 2017 at 10:35am
is your denominator the group distinct count or the report distinct count?

Posted By: fuller31
Date Posted: 02 Jun 2017 at 11:59am
Originally posted by DBlank

is your denominator the group distinct count or the report distinct count?

Group

Posted By: kevlray
Date Posted: 02 Jun 2017 at 12:11pm
FYI:  Distinct counts and grouping can be tricky depending on what you are counting on.  If there is chance that whatever you are counting on could end up in two different groups (i.e., a call number that could be in the called group and also in the cancelled group) would be counted only once in the report distinct count.

Posted By: fuller31
Date Posted: 03 Jun 2017 at 3:03am
Originally posted by kevlray

FYI:  Distinct counts and grouping can be tricky depending on what you are counting on.  If there is chance that whatever you are counting on could end up in two different groups (i.e., a call number that could be in the called group and also in the cancelled group) would be counted only once in the report distinct count.

Well, my distinct count is counting call numbers. It's a simple summary of cdcall.number with a distinct count.
The running total summarizes field wrhistry.tencod and uses the formula {wrhistry.tencod} = "CANCL" to evaluate (because I want that to only show me the number of cancels) and resets on the change of the tow company group. So, there shouldn't be any kind of interference.

Posted By: fuller31
Date Posted: 04 Jun 2017 at 3:56am
I was playing around with changing some fields and figured it out. Instead of using a distinct count of cdcall.number, I used a distinct count of wrhistry.callid. The wrhistry.callid field is linked to cdcall.number. Using the formula below, I got it to work.

{#CANCL}%DistinctCount ({wrhistry.callid}, {tbwreck.desc})


Print Page | Close Window