topic Re: How to handle ? in QlikView

How to handle ?

subbareddykm — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: How to handle ?

sunny_talwar — Tue, 08 Mar 2016 16:25:42 GMT

Not certain, but may be this:

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

Re: How to handle ?

swuehl — Tue, 08 Mar 2016 16:32:56 GMT

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
	55	Total	3404
	Total		3404
F	66	5	3404
	66	Total	3404
	Total		3404
Total			6808

Re: How to handle ?

subbareddykm — Tue, 08 Mar 2016 16:34:10 GMT

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 .

Re: How to handle ?

subbareddykm — Tue, 08 Mar 2016 16:41:28 GMT

In that case i m using expression like below :

=If(

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

)

Can u help on this?

Re: How to handle ?

swuehl — Tue, 08 Mar 2016 16:44:01 GMT

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))

Re: How to handle ?

subbareddykm — Tue, 08 Mar 2016 17:08:32 GMT

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

Re: How to handle ?

Colin-Albert — Tue, 08 Mar 2016 17:38:05 GMT

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.

Re: How to handle ?

swuehl — Tue, 08 Mar 2016 17:40:48 GMT

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?