topic Re: Pivot Chart Total - Calculation in QlikView

Pivot Chart Total - Calculation

er_mohit — Fri, 27 May 2016 10:00:24 GMT

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

Re: Pivot Chart Total - Calculation

PrashantSangle — Fri, 27 May 2016 10:03:08 GMT

Hi,

try like

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

Regards

Re: Pivot Chart Total - Calculation

sunny_talwar — Fri, 27 May 2016 10:04:14 GMT

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

Re: Pivot Chart Total - Calculation

swuehl — Fri, 27 May 2016 10:07:20 GMT

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
1003	Total		-100	0

Re: Pivot Chart Total - Calculation

er_mohit — Fri, 27 May 2016 10:08:39 GMT

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

Re: Pivot Chart Total - Calculation

PrashantSangle — Fri, 27 May 2016 10:09:53 GMT

Hi,

my bad did not read it properly check swehl reply.

Regards

Re: Pivot Chart Total - Calculation

sunny_talwar — Fri, 27 May 2016 10:11:06 GMT

max‌ what's wrong with my response brother

Re: Pivot Chart Total - Calculation

PrashantSangle — Fri, 27 May 2016 10:29:46 GMT

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

Re: Pivot Chart Total - Calculation

sunny_talwar — Fri, 27 May 2016 10:33:32 GMT

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?

Re: Pivot Chart Total - Calculation

sunny_talwar — Fri, 27 May 2016 10:39:18 GMT

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

];

Re: Pivot Chart Total - Calculation

PrashantSangle — Fri, 27 May 2016 11:10:16 GMT

Hi,

Well It was just suggestion. If particular field not necessary then avoid it.

Regards,

Re: Pivot Chart Total - Calculation

sunny_talwar — Fri, 27 May 2016 11:15:46 GMT

Agreed

Re: Pivot Chart Total - Calculation

er_mohit — Fri, 27 May 2016 12:08:53 GMT

Thanks stalwar1‌

Really Helpful.