How to handle ?

[subbareddykm]

Hi All,

I m facing one issue, in my report.

Issue is :

If Id is having more than one project or SubHead , i m getting same amount for both .....How to overcome this issue ?

Refer my screenshot in that if i select Id=5 ,Amount should be show like that for Id ( If Id is having more than one project or SubHead )

Refer my same application.

[sunny_talwar]

Not certain, but may be this:

Sum(Aggr(Avg(Amoun), Project))

[swuehl]

Sunny, I think this only works because there are as many Amoun records per Id as projects.

I haven't fully understood the requirement, I think it may be best to remodel the model.

But for a UI based solution, I assume the Sum(Amoun) per Id should be distributed equally to all Projects, and then show a sum-of-rows for the Total?

=Sum( Aggr( Sum(Amoun) / Count(TOTAL<Id> DISTINCT Project),Project,Id))

Project	SubHead	Id	Sum( Aggr( Sum(Amoun) / Count(TOTAL<Id> DISTINCT Project),Project,Id))
E	55	5	3404
		Total	3404
	Total		3404
F	66	5	3404
		Total	3404
	Total		3404
Total			6808

[subbareddykm]

One more doubt in this .

In my application, i m applying logic after sum if the value is positive rest value i m showing as Zero.becoz of that reason , in pivot table for projectwise total is not matching , if convert it into stright table row level sum its giving correct value , this kind of isssues how to solve .

[subbareddykm]

In that case i m using expression like below :

=If(

Sum(Amount)>0,Sum(Amount),0

)

Can u help on this?

[swuehl]

Seems to me like you need a sum-of-rows in pivot tables anyway (have you set total mode to sum-of-rows in the straight table?):

=Sum( Aggr(

=If(

Sum(Amount)>0,Sum(Amount),0

)

/ Count(TOTAL<Id> DISTINCT Project)

,Project,Id))

[subbareddykm]

Yes , i enabled that in stright table still i m getting in pivot table. how to solve tis?

[Colin-Albert]

I believe the issue is your data model rather than your expressions.

Project A links to SubHead 11 which links to ID 1 and the Amounts

Project B links to SubHead 22 which also links to ID 1 and the same Amounts

When you look at the total for project A , it sums both Amounts linked to ID 1 = 380

When you look at the total for project B , it sums both Amounts linked to ID 1 = 380

But when you select Projects A and B, the total is still 380 as this is the sum of the amounts associated with ID1.

The issue is your data model, not your chart.

[swuehl]

subbareddykm wrote:

Yes , i enabled that in stright table still i m getting in pivot table. how to solve tis?

Sorry, what do you get in the pivot table?

Could you modify and repost your sample QVW to demonstrate your current status?