Maximum formula on record selection 1

[aspnetuser]

Hello all,
CR 8.5
SQL server 2000

I am confused.
I have the following data:
ID Score1 Score2
1 1 1
2 1 0
3 4 3

I need to write a CR formula that first determines the MAXIMUM ID (3) and then adds score1 and score2 (7).

Can someone help me on this?

Thanks,
Regards,
aspnetuser...

 

[aspnetuser]

would you now be looking for this for each week within each ID?

Yes that is it.

Everything above is how the report is layed out. I had mentioned that i might have to add another group for week, but i was not sure.

1001
Week 1 (I guess I will have to add this ?)
tester winner

1002
Week 1 (I guess I will have to add this ?)
jason winner


there is 10 weeks per pool id...

how can we adapt to this. Remember I provided everything but the week, I am sorry about that, I was to concerned on getting the other aspects correct so that was a stupid mistake on my part...

 

[lbass]

So it would look like:

1001
Week 1 tester
Week 2 john
Week 3 Amy
Week4 Connie

1002
Week 1 jason
Week 2 susan
Week 3 larry
Etc.?

-LB

 

[aspnetuser]

LB,
yes exactly correct.

 

[lbass]

Try changing {%maxID} to

(select max(AKA.`GameID`) from Table AKA where
AKA.`Username` = Table.`Username` and
{fn Week(AKA.`Date`)} = {fn Week(Table.`Date`)})

Replace `Date` with your date field. This might be all that is necessary, although you have to make sure you are working on the inner group, e.g., when you do the topN, you want it on the Week group tab.

-LB

 

[aspnetuser]

I got it to work except for the final step.
groupnumber <> 1

That only allows 1 winner to show. If i remove that suppress formula, i see every id, by week num, with the winner sorted to the top. How do i suppress the rest but till allow every id/week winner to show?

 

[lbass]

Using the running total expert, create a running total {#cntwingrp} by choosing distinctcount of {table.user}, evaluate on change of group#3 (user group), reset on change of group#2 (Week). Then for the suppression formula for the group footer section, use:

{#cntwingrp} <> 1

-LB

 

[aspnetuser]

Perfect solution; however, sometimes their is a legit 2 way tie. eg:
user wins diff.
jason 11 1
tester 11 1

In this case I would need to repeat the running total field.
user wins diff. RT formula
jason 11 1 1
tester 11 1 1

This would show both users for this instance but still only show one for regual winners...

Would you use the x+y formula in the RT screen. I am not sure how to...

again thanks for all your help and patience. as an asp person, i am learning alot about this stuff.

 

[lbass]

Okay, ditch the running total expert and create two formulas:

//{@reset} to be placed in the Group #2 (weeks) header:
whileprintingrecords;
numbervar maxwins := 0;
numbervar mindiff := 0;
numbervar counter := 0;

//{@maxmin} to be placed in the group #3 (user) footer:
whileprintingrecords;
numbervar maxwins;
numbervar mindiff;
numbervar counter := counter + 1;

if sum({@wins},{table.user}) >= maxwins then
maxwins := sum({@wins},{table.user});
if counter = 1 or
minimum({@diffformaxID},{table.user}) <= mindiff then
mindiff := minimum({@diffformaxID},{table.user});

Then go to the section expert->group #3 (user) footer->suppress->x+2 and enter:

whileprintingrecords;
numbervar maxwins;
numbervar mindiff;

sum({@wins},{table.user}) <> maxwins or
minimum({@diffformaxID},{table.user}) <> mindiff

-LB

 

[aspnetuser]

again, that's an unbelievable talent you have there. I hope you are a crystal consultant or something???

lastly, if I wanted a version that showed all (everything up until the last step of filter just the winner(s))

user wins diff. order num.
jason 11 1 1
tester 11 1 1
jj 9 7 2
jc 8 3 3
jk 8 3 3 jc and jk get 3rd place because they tied wins and also tied the diff...

is that possible? and just not suppress the final results.

 

[lbass]

Change {@reset} to:

whileprintingrecords;
numbervar maxwins := 0;
numbervar mindiff := 0;
numbervar counter := 0;
numbervar currdiff := 0;
numbervar currwins := 0;
numbervar rank1 := 0;
numbervar rank2 := 0;
numbervar finrank := 0;
numbervar totrank := 0;

Change {@maxmin} to:

whileprintingrecords;
numbervar maxwins;
numbervar mindiff;
numbervar counter := counter + 1;
numbervar currwins;
numbervar currdiff;
numbervar rank1;
numbervar rank2;
numbervar totrank;
numbervar finrank;

if sum({@wins},{table.user}) >= maxwins then
maxwins := sum({@wins},{table.user});
if counter = 1 or
minimum({@diffformaxID},{table.user}) <= mindiff then
mindiff := minimum({@diffformaxID},{table.user});

if sum({@wins},{table.user}) <> currwins then
(currwins := sum({@wins},{table.user});
rank1 := rank1 + 1) else
rank1 := rank1;

if minimum({@difformaxID},{table.user}) <> currdiff then
(currdiff := minimum({@difformaxID},{table.user});
rank2 := rank2 + 1) else
rank2 := rank2;

if rank1 + rank2 <> totrank then
(totrank := rank1 + rank2;
finrank := finrank + 1) else
(totrank := totrank;
finrank := finrank);
finrank;

In an earlier post, I stated, "And please reveal ALL aspects of what you are looking for at one time." Yet you keep adding on new requirements. While I am glad you are learning, and I see that you are appreciative, please note that this forum is not for one-on-one training or for extended help.

-LB