Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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?
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
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.
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)
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
];
just for explanation, your expression is basically
only(
If(ACCT1=ACCT2,If( Currency='CAD',Money(Sum(Amount),'$ #,##0.00;$ -#,##0.00')),0)
)
I am not using 'only' in the expression
yes but Sum(Amount) is not giving me correct results that is why I am not trying to use Sum in my expression
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