Subtotals in pivot table

[Anonymous]

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

[MK9885]

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?

[Anonymous]

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

[MK9885]

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.

[Anil_Babu_Samineni]

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)

[Anonymous]

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
];

​

[Anonymous]

just for explanation, your expression is basically

only(

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

)

[Anonymous]

I am not using 'only' in the expression

[Anonymous]

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

[Anonymous · Accepted solution]

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