Complex Sum

[worsleyl]

CR 9 - Sybase ASA 6.0.2

Having a difficult time finding the correct way to come up with a sum....

group 1 - ServCatDesc
group 2 - AccountNo
group 3 - ActivityTypeDesc
group 4 - ActivityNo
detail - pat.masterno (may or may not be present), activitylength (always present), personid (always present)

Sum for group 2 (AccountNo) should say - ok, look at the detail lines for each activityno (group 4) - include the activitylength in the sum for group 2 for each detail line unless the personid is the same

(if this is of any assistance, in Sybase's Infomaker the expression was sum( activity_activitylength for group 2 distinct activity_activityno, personid ))

eg
Occupational Therapy
1162465
Single Ax
111
detail 1 =masterno = 771, activitylength = 60, personid = 21 - yes

Occupational Therapy
1162465
Single Ax
112
detail 2 =masterno = (null), activitylength = 15, personid = 21 - yes, even though masterno is null

Occupational Therapy
1162465
Single Ax
113
detail 3 =masterno = 772, activitylength = 90, personid = 22 - yes
detail 4 =masterno = 772, activitylength = 90, personid = 23 - yes, personid is NOT the same

Occupational Therapy
1162465
Single Ax
114
detail 5 =masterno = 773, activitylength = 120, personid = 24 - yes
detail 6 =masterno = 774, activitylength = 120, personid = 24 - NO, because the personid AND the activityno are the same as detail 5 (shows up 2x because the masterno is different)

so in this case, the group 2 sum would be 375.

I have tried a myriad of things. The closest I came was creating a formula (@activityno_personid) that added activityno and personid and then used a running total - summary - field - activitylength; evaluate - change of field - @activityno_personid); reset - change of group - accountno - however, instances arose where the result of the formula caused an incorrect duplicate - eg, activityno = 10 + personid = 21 = 31 same result as activityno = 21 + personid = 10...

All help much appreciated. Please let me know if I can provide more info or make things clearer.....

 

[RichardLB]

Have you tried a Running Total Field with
Field to summarize set to activitylength
Type of summary set to sum
Evaluate on change of personid
Reset on change of group AccountNo

 

[worsleyl]

Thanks for responding so quickly...

I just tried your suggestion but not giving me correct results - I think because the personid is not sequential (although it did look that way in my example above)....
And result was also not displayed if only one personid showed up in group 2 (ie., did not "change&quot...

 

[nagornyi]

1. Create running total formula #RT.
Fielsd to summarize: PersonID
Type of Summary: distinct count
Evaluate: for each record
Reset: On Change of Group - ActivityNo
This running total should increase only when the new value of the PersonID happens withing the ActivityNo. You can place the formula to the detail section to make sure it is working properly.

2. Create the following formula: (activitylenght_countable)

Global NumberVar T;
NumberVar P;
if {#RT} = T then P:=0 else P:=ActivityLength;
T:={#RT};
P

This formula returns activitylength if the #RT has changed on this record, i.e. this is a new PersonID for Group4. You can make this sure by placing the @activitylenght_countable into the detail section.

3. Now, you just need to summarize @activitylenght_countable for every AccountNo.

 

[worsleyl]

OK, so far so good. I completed steps 1 & 2 and placed both formulas in the detail section. I see results such as the following based on my example from before:

Occupational Therapy
1162465
Single Ax
111
detail 1 =masterno = 771, activitylength = 60, personid = 21 #PersonId_Count = 1 @activitylength_countable = 0

Occupational Therapy
1162465
Single Ax
112
detail 2 =masterno = (null), activitylength = 15, personid = 21 #PersonId_Count = 1 @activitylength_countable = 0

Occupational Therapy
1162465
Single Ax
113
detail 3 =masterno = 772, activitylength = 90, personid = 22 #PersonId_Count = 1 @activitylength_countable = 90
detail 4 =masterno = 772, activitylength = 90, personid = 23 #PersonId_Count = 2 @activitylength_countable = 90

Occupational Therapy
1162465
Single Ax
114
detail 5 =masterno = 773, activitylength = 120, personid = 24 #PersonId_Count = 1 @activitylength_countable = 0
detail 6 =masterno = 774, activitylength = 120, personid = 24 #PersonId_Count = 1 @activitylength_countable = 0


But now I'm stuck. When I try to insert a Sum in group 2 (AccountNo) footer (which I think is what you mean when you say summarize @activitylength_countable for every AccountNo) the @activitylength_countable is not available for selection (even though other formula fields are). And even if I could, do you think I would be getting the correct result? The group 2 sum should be 375, but would the above results not give me a sum of 180? Maybe I'm just not seeing it...

I'd appreciate any follow up help you can give.

 

[nagornyi]

@activitylength_countable is not calculated as supposed. OK, let's change it:

whileprintingrecords;
Global NumberVar T;
NumberVar P;
if {#RT}>1 then P:={#RT}-T else P:=1;
T:={#RT};
P;

Now the formula to result in 0 or 1. If the person is not new in the group, it gives 0, otherwise 1. Now let's manually create running total for the activitylength if it is countable.

Create the formula:

whileprintingrecords;
Global NumberVar Total;
If {@activitylength_countable}=1 then Total:=Total+{activitylength};
Total;

Put this formula to detail section and to the group footer. What is left is to reset the running totals. Create formula

whileprintingrecords;
Global NumberVar Total:=0;

and put it to the Group2 header.

 

[worsleyl]

Thanks nagornyi - you've got me thinking along the right lines! I'll work with your suggestions and get back to you!

 

[worsleyl]

Not quite there yet...Here is an example of what I'm getting, using a different example (yes/no means whether or not the activitylength should be included in the sum)

Occupational Therapy
1162465
Single Ax
113
detail 1 =masterno = 772, activitylength = 90, personid = 22 - yes
detail 2 =masterno = 773, activitylength = 90, personid = 22 - no, because the personid is the same as detail 1
detail 3 =masterno = 774, activitylength = 90, personid = 22 - no, because the personid is the same as detail 1 & 2
detail 4 =masterno = 772, activitylength = 90, personid = 23 - yes, because personid is NOT the same as detail 1, 2 or 3
detail 5 =masterno = 773, activitylength = 90, personid = 23 - no, because personid is the same as detail 4
detail 6 =masterno = 774, activitylength = 90, personid = 23 - no, because personid is the same as detail 5

The activitylength sum I am looking for is 180 - detail line 1 and detail line 4.

The scenario that results in the above is that 2 different caregivers (personid) are seeing 3 different patients (masterno) at the same time. Personid 22 should count 90 for activitylength and personid 23 should also count 90 minutes.



This is how the sum is being calculated using your suggested formulas:

detail 1 = @activitylength_countable = 1, countable_calculation = 90
detail 2 = @activitylength_countable = 1, countable_calculation = 180
detail 3 = @activitylength_countable = 1, countable_calculation = 270
detail 4 = @activitylength_countable = 0, countable_calculation = 270
detail 5 = @activitylength_countable = 0, countable_calculation = 270
detail 6 = @activitylength_countable = 0, countable_calculation = 270

Any other ideas??

 

[nagornyi]

Looks like #RT is not calculated correctly. Please insert it into details to see what's going on. Should be
1
1
1
2
2
2
Nothing complicated, just distinct count of personid within group. Once this fixed, the rest should work.

 

[worsleyl]

Hi ya
the #PersonId_Count is calculating as you say it should
1
1
1
2
2
2

However, the @activitylength_countable, which is:
whileprintingrecords;
Global NumberVar T;
NumberVar P;
if {#PersonId_Count}>1
then P:={#PersonId_Count}-T
else P:=1;
T:={#PersonId_Count};
P;

is returning
1
1
1
0
0
0

so the first three lines are calculated with the @countable_calculation formula:
whileprintingrecords;
Global NumberVar Total;
If {@test}=1 then Total:=Total+{Activity.ActivityLength};
Total;

Thanks for the effort your spending on this....

 

[nagornyi]

OK, I see the problem.
Create anoher running total #PersonId_Count_A that would just count the personid in the group - the same as first running total, but count instead of distinct count.
Then modify the formula

whileprintingrecords;
Global NumberVar T;
NumberVar P;
if {#PersonId_Count_A}>1 then P:={#PersonId_Count}-T
else P:=1;
T:={#PersonId_Count};
P;

 

[worsleyl]

Made the modifications - now output is:

detail 1 = @activitylength_countable = 1, @countable_calculation = 90
detail 2 = @activitylength_countable = 0, @countable_calculation = 90
detail 3 = @activitylength_countable = 0, @countable_calculation = 90
detail 4 = @activitylength_countable = 0, @countable_calculation = 90
detail 5 = @activitylength_countable = 0, @countable_calculation = 90
detail 6 = @activitylength_countable = 0, @countable_calculation = 90

I don't know, is there someway to incorporate "for eac distinct personid" into the @activitylength_countable formula.....

 

[nagornyi]

Provided that you changed the #PersonId_Count to #PersonId_Count_A in only ONE place, please give me the following for several groups:

personid #PersonId_Count #PersonId_Count_A @activitylength_countable

 

[Ngolem]

I am a bit confused so I will ask this question first.

"Sum for group 2 (AccountNo) should say - ok, look at the detail lines for each activityno (group 4) - include the activitylength in the sum for group 2 for each detail line unless the personid is the same"

So it is JUST if the personid is the same within the Group 4 that you don't want it summed....if the personid is a repeat in a previous activity then that is ok and you want it summed.

I'll assume then that the First value for the Personid will be taken then.

The simplest way to do this is to create one more group based on PersonID


group 1 - ServCatDesc
group 2 - AccountNo
group 3 - ActivityTypeDesc
group 4 - ActivityNo
group 5 - PersonID

Now instead of doing your calculation in the detail section...do it in the Group 5 header!

Here you will get only the first value of PersonID...if there are duplicates they won't be added

In Group 2 header put the formula

//@InitSum suppressed

WhilePrintingRecords;
numberVar TotalActLength := 0;

In the Group 5 header put

//@CalcSumActLength

WhilePrintingRecords;
numberVar TotalActLength ;

TotalActLength := TotalActLength + {Table.activitylength};

Then display the result in Group 2 footer

This should work



Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[worsleyl]

Nagornyi and Jim - I tried Jim's suggestion and it worked like a charm! I want to thank you both. Your assistance is much appreciated. And Nagornyi, if you want me to follow-up on your question, please let me know...

 

[nagornyi]

I agree that creating another group as Ngolem suggested is the best solution. I contemplated this solution from the very beginning but assumed that having created 4 groups in the report, there should be a reason for not creating the obvious fifth group. The reason as I believed is that the records may not necessarily be allowed to rank by personid.
This problem however raised another interesting problem which I suggested for a separate discussion thread767-739224.

 

[Ngolem]

thank you...I'l look at your other problem

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!