| Author |
Message |
Deanna
Newbie
Joined: 18 Feb 2010
Location: United States
Online Status: Offline
Posts: 9
|
  
Topic: Distinct Counts
Posted: 18 Feb 2010 at 3:20pm |
Hello~
I am attempting to do distinct counts summaries and ever single one of them is adding up incorrectly by 1. So if the number is supposed to be 30 it is telling me I have 31 records. Has anyone run into this before?
I am doing a distinct count on a formula. The formula returns a ticket number if the date is in the month 10. Then what I need to do is count the number of unique ticket id's to give me the number for the month. I need to do this for a year time period so I need to find a find to decipher and count up every month separately. But this is one issue that I am dealing with.
if month ({INCIDENT_REVIEW.INCIDENT_DATE}) = 10
then {INCIDENT_REVIEW.INCIDENT_REVIEW_ID_JOIN}
Any help would be appreciated.
Deanna
|
|
Deanna
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 19 Feb 2010 at 6:58am |
only a guess/question, what happens if they aren't part of the date? If say they are assigned another value (NULL, "", 0) wouldn't they be picked up as a 'distinct' item. I know it is counter intuitive, and I know that I wouldn't have thought this way, but could it be happening?
I use formulas with global/shared variables, and I increment them, so I have never run into this situation. I would have thought that your formula would have returned the correct value, but if it is ALWAYS off by 1, and it is 1 too many, this may be the case, or just subtract 1.
Just a thought, hope it helps.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 19 Feb 2010 at 7:41am |
Lockwelle is correct in that all of the records that are <>10 are all considered a "" and that is a distinct value so it is increasing your value by 1. It would ignore a NULL but you cannot make a crystal formula return a NULL to avoid the count.
Just use variable as lockwelle indicates or a conditionaly Running Total.
RT option as:
Name=DateCount
Field to Summarize={INCIDENT_REVIEW.INCIDENT_REVIEW_ID_JOIN}
Type=DistinctCount
Ecvaluate=Use a formula...
month ({INCIDENT_REVIEW.INCIDENT_DATE}) = 10
Reset= on change of group (at your month level).
Place on the group footer to see the values
|
| IP Logged |
|
Deanna
Newbie
Joined: 18 Feb 2010
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 19 Feb 2010 at 8:23am |
The variable sounds like it may have to be the way to go. I am not up to speed on how to create variables. Most of my programming was years ago without the opportunity to utilize it.
As for the Running Totals, that was how it was set up originally as you have it DBlank and it is also not coming up with the correct figure. It is actually giving me random less counts then what actually exists.
I can't do a cross tab report because the user want to see quarter data rolled up and then a final year column. Would I need to create a variable for each month to get a column for each month and then another for each total?
This is what I need to have as a final report Group 1 is the Element Group 2 is the Severity:
Element2:
Severity-Oct-Nov-Dec-FYQ1-Jan-Feb-Mar-AprFYQ2-.......YTD
Sev1- #- #- # -Total-#- #- #- Total -..........Total
Sev1a- #- #- # -Total-#- #- #- Total -..........Total
Sev1b- #- #- # -Total-#- #- #- Total -..........Total
Sev2- #- #- # -Total-#- #- #- Total -..........Total
Sev1a- #- #- # -Total-#- #- #- Total -..........Total
Total of each column for Element 1
Element2:
Severity-Oct-Nov-Dec-FYQ1-Jan-Feb-Mar-AprFYQ2-.......YTD
Sev1- #- #- # -Total-#- #- #- Total -..........Total
Sev1a- #- #- # -Total-#- #- #- Total -..........Total
Sev1b- #- #- # -Total-#- #- #- Total -..........Total
Sev2- #- #- # -Total-#- #- #- Total -..........Total
Sev1a- #- #- # -Total-#- #- #- Total -..........Total
Total of each column for Element 2
Then at the bottom I need a total for each column for all elements. Originally the report was set up with a ton of running totals, but after data verification they are not totalling correctly. They are actually less than what the total should be.
Thank you for your help.
|
|
Deanna
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 19 Feb 2010 at 8:31am |
|
Are the RTs placed in a section below ALL data rows that is needs to count/read? RTS must read through the data and then display it. If it has data rows below it it will only display a count/sum of the rows that it has read.
Edited by DBlank - 19 Feb 2010 at 8:33am
|
| IP Logged |
|
Deanna
Newbie
Joined: 18 Feb 2010
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 19 Feb 2010 at 8:57am |
Edited by Deanna - 19 Feb 2010 at 9:01am
|
|
Deanna
|
| IP Logged |
|
Deanna
Newbie
Joined: 18 Feb 2010
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 19 Feb 2010 at 8:59am |
I believe so.
This is what I have as the running total:
Summarizing INCIDENT_REVIEW_CONTRIB_FACT.INCIDENT_REVIEW_ID_JOIN as a Distinct Count
Evaluating as a formula Month ({INCIDENT_REVIEW.INCIDENT_DATE}) = 10
Resetting on Change of Group Group#2:INCIDENT_REVIEW.INCIDENT_SCORE - A
I have the totals in the group footer of Severity (SCORE) and Element depending on what they are summarizing.
Edited by Deanna - 19 Feb 2010 at 9:01am
|
|
Deanna
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 19 Feb 2010 at 11:12am |
It is hard to decipher where the issue for your report but I am confused as to why you are not using a Cross tab?
For your Rows set it to Element 2 and then Severity field
For your columns set it to incident date and then change that group option to Quarterly. Then add the Incident data again and set that one to Monthly.
then for your summarized field use the ReviewId Join set as a Distinct Count.
You will have to play with the style and formatting but the data shoyuld be correct. Edited by DBlank - 19 Feb 2010 at 11:13am
|
| IP Logged |
|
Deanna
Newbie
Joined: 18 Feb 2010
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 19 Feb 2010 at 11:27am |
That may work but how do I get the cross tab to show each month and quarter? From the looks of it, it is an either or, not both, which I need to have.
|
|
Deanna
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 19 Feb 2010 at 11:30am |
Add the date field you need to group on to the "columns" window twice.
The first (top) one change the "group option" to Quarterly.
The second one change it to Monthly.
|
| IP Logged |
|
|
|
