| Author |
Message |
fuller31
Newbie
Joined: 29 May 2017
Location: United States
Online Status: Offline
Posts: 22
|
   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 Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
fuller31
Newbie
Joined: 29 May 2017
Location: United States
Online Status: Offline
Posts: 22
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
fuller31
Newbie
Joined: 29 May 2017
Location: United States
Online Status: Offline
Posts: 22
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
fuller31
Newbie
Joined: 29 May 2017
Location: United States
Online Status: Offline
Posts: 22
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 02 Jun 2017 at 10:35am |
|
is your denominator the group distinct count or the report distinct count?
|
|
IP Logged |
|
fuller31
Newbie
Joined: 29 May 2017
Location: United States
Online Status: Offline
Posts: 22
|
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 Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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 Logged |
|
|
|
