Pivot Chart Total - Calculation

[er_mohit]

Hello friends.

I am stuck off while doing calculation in pivot chart.

Please find below the table details with output. I have a table like this

LOAD * Inline [

ID,SubId,SubId2,Sale,sale,Stock

1001,a,a1,20,40,10

1001,b,b1,-20,40,60

1001,c,c1,30,40,60

1002,d,d1,30,60,70

1002,a,a1,40,60,80

1003,z,z1,-50,40,90

];

On front side i am creating pivot table.

Used one expression : (sum(Sale)+sum(sale))-sum(Stock) -  Total Sale

Now I want output like this :

ID	SubId	SubId2	Total Sale
1001	a	a1	50
1001	b	b1	-40
1001	c	c1	10
1001	Total		60
1002	a	a1	20
1002	d	d1	20
1002	Total		40
1003	z	z1	-100
1003	Total

Here what i want to show in Total field ignore negative values consider only positive while doing totalling and if there is only negative value then total field should be null.

I am using dimensionality() function to do the same but not getting the desired result.

Please find attachment also.

Any help will be appreciable.

Regards

Mohit

[PrashantSangle]

Hi,

try like

if((sum(Sale)+sum(sale))-sum(Stock)>0,(sum(Sale)+sum(sale))-sum(Stock))

Regards

[sunny_talwar]

May be this:

If(Dimensionality() = 1, Sum(Aggr(If((sum(Sale)+sum(sale))-sum(Stock) > 0, (sum(Sale)+sum(sale))-sum(Stock)), ID, SubId, SubId2)),

(sum(Sale)+sum(sale))-sum(Stock))

UPDATE: or this if you want all totals to exclude negatives

If(Dimensionality() = 3, (sum(Sale)+sum(sale))-sum(Stock), Sum(Aggr(If((sum(Sale)+sum(sale))-sum(Stock) > 0, (sum(Sale)+sum(sale))-sum(Stock)), ID, SubId, SubId2)))

[swuehl]

If(Dimensionality()=1,

Sum(Aggr(If(

((sum(Sale)+sum(sale))-sum(Stock))>=0 ,

(sum(Sale)+sum(sale))-sum(Stock))

,ID,SubId)),

(sum(Sale)+sum(sale))-sum(Stock)

)

ID	SubId	SubId2	Total Sale	Sale1
1001	a	a1	50	50
	b	b1	-40	-40
	c	c1	10	10
	Total		20	60
1002	a	a1	20	20
	d	d1	20	20
	Total		40	40
1003	z	z1	-100	-100
	Total		-100	0

[er_mohit]

Hi

Thanks for your reply.

I think you didn't get what i am trying to say. I have already tried this. but it gives null value where negative comes.

I want to show the negative value but in total field while doing partial sum it should be the sum of positive value not consider negative.

By default it gives the sum after knock off. positive no and negative no. but my rquirement is to consider only positive no in total field.

Regards

Mohit

[PrashantSangle]

Hi,

my bad did not read it properly check swehl reply.

Regards

[sunny_talwar]

max‌ what's wrong with my response brother

[PrashantSangle]

Sorry bro I am replying through Inbox so did not saw your reply.

but your answer also correct.

Just one suggestion avoid unwanted field in aggr()

It is harmful.

Regards

[sunny_talwar]

In current example we have subid and subid2 as one-to-one match. What happens if we have multiple subid2 within a single subid. Would your above statement still hold true under those circumstances?

[sunny_talwar · Accepted solution]

Not that I know the data very well, but just by adding a single row to the data and see the difference

LOAD * Inline [

ID,SubId,SubId2,Sale,sale,Stock

1001,a,a1,20,40,10

1001,b,b1,-20,40,60

1001,b,b2,80,40,60

1001,c,c1,30,40,60

1002,d,d1,30,60,70

1002,a,a1,40,60,80

1003,z,z1,-50,40,90

];