Distinct Sum functionality 1

[damonkimble]

Using Crystal 10 connecting to MS SQL via ODBC.

Basically, I'm looking to do a 'distinct sum.' I am trying to calculate average age across groups that can contain the same persons more than once. As an example, Chone Figgans plays both the infield and outfield for the Angels and shows up in both lists. Using running totals, I am able to get a distinct count of 12, but his age is being added in twice as I can't figure out how to only add it in once. I can post some code if needed, but the example covers the concepts pretty well. Any thoughts would be greatly appreciated!

thank,
damon


Infielders Age
Orlando Cabrera 31
Darin Erstad 31
Chone Figgins 27
Adam Kennedy 29
Casey Kotchman 22
Dallas McPherson 25
Kendry Morales 22
Robb Quinlan 28
---------------------------------------
Infield Totals (8 players w/average age of 26.88) - (215 years / 8 players)

Outfielders Age
Garret Anderson 33
Chone Figgins 27
Steve Finley 40
Vladimir Guerrero 29
Juan Rivera 27
---------------------------------------
Outfield Totals (5 players w/average age of 31.20) - (156 years / 5 players)


Totals (12 players w/average age of 28.67) - (344 years / 12 players)
** Totals (371y / 12p) = 30.92 - this is what my running totals gives me right now

 

[synapsevampire]

Insert a cross-tab in the report header or report footer and place the player in the Row, and the age in the Summary field, select the age and select change summary, and make it an average.

This will give you the overall average using each player once.

This result won't jive with your grouped results though, as you are apporpriately demonstrating the averages by infield and outfield.

-k

 

[damonkimble]

For formatting reasons, it was easier to send it out as text, so it's not a quick test for me. However, having the row won't work as it will show every distinct player and age. Need to just summarize/average. This should get me going in the right direction and I'll pick up in the morning...

thanks,
damon

 

[lbass]

I think you could omit the row in the crosstab and just use average of age as the summary field.

-LB

 

[damonkimble]

This doesn't do the calculation right. Rather than returning 28.6666666, it returns 28.54. I can't figure out how it came up with that number. I also can't logically figure out how it would work anyway as it has no idea to 'group' by the id.

I would think that a running total (at each grouping level) that skips if the id's value has already been added and then dividing by distinct count of id (this count isn't a problem). Not sure how to do that. I've looked at trying to use a sum inside a header or footer, but haven't had any luck with that either...

thanks,
damon

 

[lbass]

Do you have any section suppression or group selection formulas? Also, is age a calculation where you are displaying a rounded number versus a field that is an integer?

-LB

 

[damonkimble]

I'm getting it done with subreports. I've gotten the example used done and am working on some more complex aspects of it that I hadn't worked into the question. I'll post details a bit later.

damon

 

[lbass]

You could have created a formula like the following for the detail section:

whileprintingrecords;
stringvar player;
numbervar age;

if instr(player,{table.player}) = 0 then
(
player := player + {table.player};
age := age + {table.age}
);

Then in the report footer, you could have used a formula like:

whileprintingrecords;
numbervar age;
age/distinctcount({table.player})

-LB

 

[damonkimble]

Thanks LB! This works a lot cleaner and quicker than a bunch of subreports. I added a delimiter to ensure there weren't duplicated added (i.e. 10 being counted again for 100 - 109, 1000 - 1099, etc.).

One more question on this. I've actually got a couple of levels of groups. Is there a way to copy and modify these formulas to empty the stringvar on the first record of each group?

cheers,
damon


whileprintingrecords;
stringvar p_id;
numbervar p_total;

if instr(p_id,'~' + totext({Command.tp_id}) ) = 0
then (p_id := p_id + '~' + totext({Command.tp_id});
p_total := p_total + {Command.tp_birth_int}
);

 

[damonkimble]

Make that two questions I've got a few people who don't have a birthdate listed and do not want to include them in the distinct count. I've got some running totals going from previous attempts that I could use, but wondering if there is an easier way.

damon

 

[lbass]

You don't have to test for the presence of the delimiter, if you format the ID so that it is always the same length. So your formula could be:

whileprintingrecords;
stringvar p_id;
numbervar p_total;

if instr(p_id, totext({Command.tp_id},"0000") ) = 0
then (p_id := p_id + '~' + totext({Command.tp_id},"0000");
p_total := p_total + {Command.tp_birth_int}
);

You would use a separate reset formula like the following in the group header:

whileprintingrecords;
stringvar p_id := "";
numbervar p_total := 0;

If you want the results to appear at several different group levels, you will need to use separate variables with different resets for each set. This means a separate reset formula and separate display formula for each group, using unique variable names, and one formula in the detail section where you do the accumulation multiple times, as in:

whileprintingrecords;
stringvar p_id;
numbervar p_total;
stringvar p_id2;
numbervar p_total2;

if instr(p_id, totext({Command.tp_id},"0000") ) = 0
then (p_id := p_id + '~' + totext({Command.tp_id},"0000");
p_total := p_total + {Command.tp_birth_int}
);
if instr(p_id2, totext({Command.tp_id},"0000") ) = 0
then (p_id2 := p_id2 + '~' + totext({Command.tp_id},"0000");
p_total2 := p_total2 + {Command.tp_birth_int}
);

//etc.

-LB

 

[damonkimble]

Even with all the id's the same length, you can run into the same pattern across two fields. I actually erred in leaving off a trailing delimiter as well. It should be '~ field |' or something of that nature.

I was running into issues having all of the detail formulas together, so I split out and put them all in a suppressed detail section. This was beneficial in troubleshooting as well. I had to add a not(InRepeatedGroupHeader) in the reset formulas as I am repeating group headers and I don't want counts resetting on every page!

Many thanks to LB for getting me going in the right direction!


For the benefit of anyone trying to figure out how to do this also, I've summarized what I had to do. This is from the actual report, not the theoretical one (which I actually ended up making to test) which I have referred to in previous posts.

Group Header - Group reset formulas
Detail - Summing formulas
Group and/or Report Footer - use the results!


Reset formula placed in group header. I've got all the reset formulas for this group here even though I'm only showing g3_bi in the rest of the examples.

if not(InRepeatedGroupHeader)
    then (
whileprintingrecords;
stringvar g3_bi_id := "";
numbervar g3_bi_sum := 0;

stringvar g3_st_id := "";
numbervar g3_st_sum := 0;

stringvar g3_en_id := "";
numbervar g3_en_sum := 0;
);

One of the detail level summing functions. As per LB, can put them all in one formula but I found it easier to separate each one out and put them in a surpressed detail section for troubleshooting purposes. Also note that I am summing a field that is a date cast as an int.

whileprintingrecords;
stringvar g3_bi_id;
numbervar g3_bi_sum;

// G3 Birth
if instr(g3_bi_id, '~~' + {PE_CatchAll.person_id} + '||') = 0
then (g3_bi_id := g3_bi_id + '~~' + {PE_CatchAll.person_id} + '||';
        g3_bi_sum := g3_bi_sum + {PE_CatchAll.birthdate_int}
    );

// Running Total (may not be needed)
I used a running total (#Group3_AgeCount) to count distinct ids as some can have NULL dates and those aren't added in the sum. A straight distinctcount would have include those.



I'm using a custom function to print out the average age in a '25y 6m 7d' format. This is located in the group footer.

whileprintingrecords;
numbervar g3_bi_sum;

cfAgeFull (
    dateadd('d'
        , g3_bi_sum / {#Group3_AgeCount}
        , dateserial(1900,1,1)
        )
    , {?ToDate}
    , 'c'
    )