topic Subtotals in pivot table in QlikView

Subtotals in pivot table

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

Hi All

I am creating a pivot table with two fields Amount and Net.Expressions are as follows:

If(ACCT1=ACCT2,If( Currency='CAD',Money(Sum(Amount),'$ #,##0.00;$ -#,##0.00')),0),but I need an expression without using sum field in my expression like If(ACCT1=ACCT2,If( Currency='CAD',Money(Amount,'$ #,##0.00;$ -#,##0.00')),0).I am getting correct values for this expression,but problem is it is not showing subtotals since I am not using sum in the expression.

Can anyone please help so that I can get subtotals also by using second expression

Re: Subtotals in pivot table

MK9885 — Fri, 03 Feb 2017 16:50:56 GMT

It doesn't matter if you using Sum or Count.

Partial sum will come when you enable that option in 'Participation Tab'

Here I'm using 1 as an expression and it still gives me subtotals.

Correct me if I'm wrong and is this what you looking for?

Re: Subtotals in pivot table

Anonymous — Fri, 03 Feb 2017 17:01:03 GMT

Hi Aehman

I am getting null values when I am not using sum in the expression

when I am using Sum it is returning a value as shown above

Re: Subtotals in pivot table

MK9885 — Fri, 03 Feb 2017 17:04:19 GMT

After adding Sum to your expression, go to Presentation Tab in properties of chart and enable the 'Show partial sum' option.

If still finding issue, can you upload the sample qvw?

Thanks.

Re: Subtotals in pivot table

Anil_Babu_Samineni — Fri, 03 Feb 2017 17:04:40 GMT

May be try in script level for Sum(amount) as amount1 by using Group By of associate field and then try something like

If(ACCT1=ACCT2,If( Currency='CAD',Money(amount1,'$ #,##0.00;$ -#,##0.00')),0)

Re: Subtotals in pivot table

Anonymous — Fri, 03 Feb 2017 17:05:02 GMT

In my opinion "show partial sum" is an absolutely misleading description, because it is not a sum, it is an expression within certain dimensions... I rather would call it "group by dimension".

You could make use of aggr()

LOAD * INLINE [
F1, F2
A, 1
B, 2
C, 3
D, 4
E, 5
F, 6
G, 7
];

Re: Subtotals in pivot table

Anonymous — Fri, 03 Feb 2017 17:10:03 GMT

just for explanation, your expression is basically

only(

If(ACCT1=ACCT2,If( Currency='CAD',Money(Sum(Amount),'$ #,##0.00;$ -#,##0.00')),0)

)

Re: Subtotals in pivot table

Anonymous — Sat, 04 Feb 2017 07:42:37 GMT

I am not using 'only' in the expression

Re: Subtotals in pivot table

Anonymous — Sat, 04 Feb 2017 07:43:42 GMT

yes but Sum(Amount) is not giving me correct results that is why I am not trying to use Sum in my expression

Re: Subtotals in pivot table

Anonymous — Sat, 04 Feb 2017 08:53:01 GMT

If you're not using only you're using only ...

(have a look at https://community.qlik.com/blogs/qlikviewdesignblog/2012/08/03/the-only-function)

only(

If(ACCT1=ACCT2,If( Currency='CAD',Money(Sum(Amount),'$ #,##0.00;$ -#,##0.00')),0)

)

is exactly the same as

if(ACCT1=ACCT2,If( Currency='CAD',Money(Sum(Amount),'$ #,##0.00;$ -#,##0.00')),0)

I prefer using only(...), because I think it is easier to maintain an overview.

Here's my example "without" only()

You have to adapt it to your needs...

Example:

I suppose you have 3 dimensions within your pivot table.

Let's call them D1, D2, D3...

Then your aggr() expression should look like that:

sum(

aggr(

if(ACCT1=ACCT2,If( Currency='CAD',Money(Sum(Amount),'$ #,##0.00;$ -#,##0.00')),0)

,D1,D2,D3)

)

Regards

Robin

Re: Subtotals in pivot table

Anonymous — Sat, 04 Feb 2017 09:19:30 GMT

Now I see your problem, you make use of nested aggregations...

try:

Sum(If(ACCT1=ACCT2,If( Currency='CAD',Money((Amount),'$ #,##0.00;$ -#,##0.00')),0))

instead...