Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Percentage using distinct count and running total Post Reply Post New Topic
Page  of 2 Next >>
Author Message
fuller31
Newbie
Newbie


Joined: 29 May 2017
Location: United States
Online Status: Offline
Posts: 22
Quote fuller31 Replybullet Topic: Percentage using distinct count and running total
    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

Edited by fuller31 - 02 Jun 2017 at 6:26am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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})

Edited by DBlank - 02 Jun 2017 at 7:12am
IP IP Logged
fuller31
Newbie
Newbie


Joined: 29 May 2017
Location: United States
Online Status: Offline
Posts: 22
Quote fuller31 Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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})
IP IP Logged
fuller31
Newbie
Newbie


Joined: 29 May 2017
Location: United States
Online Status: Offline
Posts: 22
Quote fuller31 Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
fuller31
Newbie
Newbie


Joined: 29 May 2017
Location: United States
Online Status: Offline
Posts: 22
Quote fuller31 Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 02 Jun 2017 at 10:35am
is your denominator the group distinct count or the report distinct count?
IP IP Logged
fuller31
Newbie
Newbie


Joined: 29 May 2017
Location: United States
Online Status: Offline
Posts: 22
Quote fuller31 Replybullet Posted: 02 Jun 2017 at 11:59am
Originally posted by DBlank

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

Group
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet 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.
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
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



This page was generated in 0.027 seconds.