topic Re: Partial sum issue in pivot table. in QlikView

Partial sum issue in pivot table.

jduluc12 — Tue, 17 Oct 2017 12:42:34 GMT

Hi,

Here is my data set

Aux:

LOAD * INLINE [

ID, Amount1, Type

1, 100, A

1, 130, B

2, 230, A

2, 130, A

2, 140, B

3, 100, A

3, 200, A

3, 300, A

3, 500, A

4, 200, A

4, 300, B

4, 500, A

];

Main:

LOAD * INLINE [

ID, Amount2

1, 100

2, 230

3, 500

4, 500

];

I am creating a Pivot table and the purpose of this table is to show

Amount from Amount1 Column if there is Type B associated with the particular ID else take the amount from Main.

Dimension:

Type

Expression:

IF(sum({<ID=,Type={'B'}>}(if(Match(Type,'B')>0,Amount1)))=0,

sum(Amount2),

sum({<ID=,Type={'B'}>}(if(Match(Type,'B')>0,Amount1))))

The individual Amount for each ID is right but the Total is not considering the value of ID 3 which is 500. The difference between ID 3 and others is that the value of ID 3 is coming from the Main table unlike others.

Why is that?

Re: Partial sum issue in pivot table.

sunny_talwar — Tue, 17 Oct 2017 12:45:19 GMT

Try this

Sum(Aggr(

IF(sum({<ID=,Type={'B'}>}(if(Match(Type,'B')>0,Amount1)))=0,

sum(Amount2),

sum({<ID=,Type={'B'}>}(if(Match(Type,'B')>0,Amount1))))

, ID))

Re: Partial sum issue in pivot table.

Anil_Babu_Samineni — Tue, 17 Oct 2017 12:46:52 GMT

Try to enable default option like Sum.

Or else, Can this works for you

Sum(Aggr(

IF(sum({<ID=,Type={'B'}>}(if(Match(Type,'B')>0,Amount1)))=0,

sum(Amount2),

sum({<ID=,Type={'B'}>}(if(Match(Type,'B')>0,Amount1))))

, ID))