Formula to SUM distinct records 1

[rbh123456789]

CR 11.5

//@formula
if isnull({temp_rep_app.represent_TYPE}) then 1
else if {temp_rep_app.APP_ID} <> {temp_rep_app.REP_ID} then 1
//

The above formula contains the logic of which records i want to qualify for the count.

Each of the records have a CASEID, which is the main link between all the tables.

The problem if that i have multiple records for many of the CASEIDs.

How can i sum up the above formula to only select the DISTINCT CASEIDs?

 

[jlopeman]

Group by CASEID and do a running total with a reset on CASEID group.

 

[rbh123456789]

Thanks for the response. I created the running total, and also created a crosstab. The columns use a DateField (grouped by month) and i put the RT in the summarized field.

The RT is summarizing the:
//@formula
if isnull({temp_rep_app.represent_TYPE}) then 1
else if {temp_rep_app.APP_ID} <> {temp_rep_app.REP_ID} then 1
//

Which evaluates on the change of CASEID group and RESETS on the datefield.

However, this is not giving me the correct totals per month. Any thoughts?

 

[MCuthill]

rbh,

Perhaps:

//@formula
if isnull({temp_rep_app.represent_TYPE}) then {CASEID}
else if {temp_rep_app.APP_ID} <> {temp_rep_app.REP_ID} then {CASEID}

Then, this formula to remove the increment for the nulls.

{@RemoveNull}
DistinctCount ({@formula})-1

This second formula assumes a report footer total, if on a group, adjust the DistinctCount() syntax appropriately.

Hope this helps.

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

Try the following:

if isnull({temp_rep_app.represent_TYPE}) or
{temp_rep_app.APP_ID} <> {temp_rep_app.REP_ID} then
{CASEID} else
tonumber({@null})

...where {@null} is a new formula that you open and save without entering anything. You can then insert a distinctcount on this without using a running total. If {caseID} is a number, remove tonumber().

MCuthill,

The problem is that your result will be incorrect in cases where the group or report contain no default values.

-LB

 

[rbh123456789]

Thanks everyone,

However, i have decided to go in a new direction with the report, which will contain less logic.

In my crosstab i have 3 rows (columns are dates by month):
1. Total Number of Unique Cases
2. Unique cases with a Representative
3. Unique cases without a Representative

For #1, i have the summarized field in the CT performing a DistinctCount on the Cases
For #2 i have the below forumla being summarized with a DistinctCount:

//@tmp_represented
if {temp_rep_app.COMPPOID} = {temp_rep_app.REPRESENT_PSOID} then {temp_rep_app.id}

For #3, i want (#1 from above) - (#2 from above), which would give me the difference, and display how many Unique cases were Unrepresented.

Any ideas on how to do this?

 

[lbass]

Please read my post. You will get the incorrect number using your formula--at least sometimes.

-LB

 

[rbh123456789]

Hi lbass, thank you.

The formula in my most recent post has not been mentioned before. my logic has changed since the original question.

 

[lbass]

Yes, but the logic is flawed. Whenever the criterion is not met, the default value will apply, and your distinctcount will count that, too. Please see my earlier post where I explcitly added {@null}--you need to do this.

-LB

 

[rbh123456789]

Sorry, im really not trying to be difficult.

I have already added the @null formula to my #2 statement above, and it is working fine. #1 is also doing what is indented. however, #3 is what i am struggling to calculate...

 

[lbass]

Instead of subtracting, try something like this for the unrepresented:

if isnull({temp_rep_app.REPRESENT_PSOID}) then
{temp_rep_app.id} else
tonumber({@null})

...assuming {temp_rep_app.REPRESENT_PSOID} is the field that identifies a representative.

-LB

 

[rbh123456789]

Now that i have dived even further through the data, i don't believe i have any way of properly counting up the Unrepresented cases.

Counting the distinct cases works of course, counting the distinct cases with Representation (when represent_psoid = comppoid) works too.

But since there are sometimes multiple rows per case, i would need to 'tell' crystal to distinctcount the cases but skip the CaseID groups that have a rep (based on the {@tmp_represented} formula)

 

[lbass]

Sure there is. You need to check whether the maximum of the representative field per case group is null. You can do this in a running total that does a distinctcount of case and uses an evaluation formula:

(
isnull(maximum({table.rep},{table.case})) or
trim(maximum({table.rep},{table.case})) = ""
)

Reset on change of date (monthly).

Or you could try creating a SQL expression {%maxrep}:

(
select max(`rep`)
from table A
where A.`caseID` = table.`caseID`
)

Then you could create a formula like this:

if isnull({%maxrep}) or
trim({%maxrep}) = '' then
{table.caseID} else
tonumber({@null})

-LB

 

[rbh123456789]

Wow, this is a very good idea. thank you for your patience.
That RT would be perfect if i also contained the logic 'if comppoid <> represent_psoid then evaluate'

I have another attachment of the records i am speaking of.

 

[lbass]

I don't know what your running total is trying to do in your last example.

-LB

 

[rbh123456789]

the RT is using your evaluate formula from above:
((
isnull(maximum({table.rep},{table.case})) or
trim(maximum({table.rep},{table.case})) = ""
))

the field that the RT is summarizing is the caseID, summary type is a distinctcount, and the RT will reset based on the month.

 

[lbass]

Please show your ACTUAL formula.

-LB

 

[rbh123456789]

The RT 'evaluate formula' is:

(
isnull(maximum({temp_rep_app.REPRESENT_PSOID},{temp_rep_app.id})) or
trim(maximum({temp_rep_app.REPRESENT_PSOID},{temp_rep_app.id})) = ""
)

@tmp_represented is:

if {temp_rep_app.COMPPOID} = {temp_rep_app.REPRESENT_PSOID} then {temp_rep_app.id} else tonumber({@null})

 

[lbass]

In addition to the actual rt formula, show the content of your formula {@tmp_represented} formula.

I'm also not sure what you want counted--count if represenetative_psoid is null or if it doesn't match the comppoid?

-LB

 

[rbh123456789]

@tmp_represented is:

if {temp_rep_app.COMPPOID} = {temp_rep_app.REPRESENT_PSOID} then {temp_rep_app.id} else tonumber({@null})

Correct, if represent_psoid is null OR if comppoid and represent_psoid do not match is what the RT should count.

However, if the CaseID group has a value from the @tmp_represented formula, then do not evaluate.