How to a do a sum of sum on Header level 1

[vega83]

Friends I have data this way

Gh1
WHOLE total-----2
GH2
ZZ total--- 1
details
abc 1
abc 0
GH2
SS total---0
details
abc 0
abc 0
GH2
CO total---1
details
abc 1
abc 1

But in GH 1 when i do sum it summing up every detail and giving me value 3. Is there a formula I can use to display it as 2?
Thanks in advance,
Vega.

 

[lbass]

Well, what is the summary you are using at the GH2 level--it can't be a sum, since in your third instance, the summary = 1. Is it a distinctcount? A maximum?

-LB

 

[vega83]

I have a formula if date1 < date2 then 1 else 0 in details.
In header i used same formula too

 

[vega83]

LB every 1st group will have multiple groups 2 with different date . Basically i am trying to sum (total value of GH2). Thanks so much for your help on this

 

[lbass]

If you place that in the header, it will just return the result for the first record in the group. What is your intention?

-LB

 

[vega83]

that is right for each ticket it has only one data but each ticket has mutiple subtickets. I want to add up only GH2 vlaues in GH1.

 

[vega83]

I can do max(date1,gh2) > max(date2,gh2)then 1 else 0. But i cannot sum this again. If there is a turnaround for this it would be very helpful

 

[lbass]

I think you could concatenate your group #1 field ({table.ticket}?) with the date field in a formula like this:

{table.ticket}+" "+totext({table.date},"yyyy-MM-dd")

Then insert a distinctcount on this formula at the Group #1 level and drag the result into the group header.

-LB

 

[vega83]

LB, on which date field i need to do it? I have two data fields? calculation is if datefield 1 < datefield 2 then 1 else 0. I dont use current date. Sorry i did not mention earlier.

 

[lbass]

Sorry, I lost track of that. What is the purpose of this comparison? Are the two dates the same for each record within the Group #2?

-LB

 

[vega83]

one is date is ticket opned and other is ticket closed . If open date < close data then i calculate it as one.

GH2 has multiple GH3

data is liekt his


GROUP A---GH1----total 2

ticket 1 GH2 total 1
child tick1 1 ----detailas
child tick2 0


ticket 2 GH2 total---0
child tick1 0 ----detailas
child tick2 0

ticket 3 GH2 total 1
child tick1 1 ----detailas
child tick2 1

 

[lbass]

Okay, try this:

First create a formula {@null} by opening and saving a new formula without entering anything. Then create a formula:

if {table.opendate} < {table.closedate} then {table.ticketnumber} else
tonumber({@null}) //remove the tonumber if {table.ticketnumber} is a string

Place this in the detail section and insert a distinctcount on it at the Group #1 level. Drag the result into the group header.

-LB

 

[vega83]

LB details will have d1 and d2 of GH2

ig GH2 has date1(08-08-2009) date2(10-08-2009)
Details
Childticket 1
childtciket 2 will have same dates date1(08-08-2009) date2(10-08-2009)


So basically what ever i have summarized in GH2 should be again summaried on GH1

 

[lbass]

Please try my suggestion.

-LB

 

[vega83]

LB got it I use your formula and said
if {table.opendate} < {table.closedate} then GH2 field else
{null}. It works!...Thanks a ton!

 

[vega83]

LB any way to do average on avergae? I want to to do Average of GH2 on GH1

 

[lbass]

Average of what? GH2 is a section.

-LB

 

[vega83]

I have other field called no of days

All the child tickets will have same no of days value like below

GROUP A---GH1----total (40+20+30)/3(distinct GH2 field) = 30

ticket 1 GH2 total 40
child tick1 40 ----detailas
child tick2 40


ticket 2 GH2 total---20
child tick1 20 ----detailas
child tick2 20

ticket 3 GH2 total 30
child tick1 30 ----detailas
child tick2 30

 

[vega83]

Friends can this be done?, any help would be great

 

[lbass]

If you are planning a variety of calculations in the group #1 header, you are better off saving the report as a subreport and then inserting it in the group #1 header, linked on the group #1 header field. Then you can use running totals to the calculations.

-LB