topic Re: Sub Totals in Pivot Table in QlikView

Sub Totals in Pivot Table

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

Hi All

I have a requirement where I am using expressions for amount and sale as:

Amount:

Num(sum(aggr(avg(MRC11),[Account Number],[Description])) ,'$#,##0.00'),

Sale:

Num(Aggr(SUM(Net),[Account Number],[Description]) ,'€#,##0.00'),

If I am applying above expressions with aggr I am getting null values.Please help me with this.

ExpectedAmount	ActualAmount	ExpectedSale	ActualSale
22	22	12	12
34	34	12	12
34	34	2	2
Total=56	Total=-	Total=26	Total=-

Re: Sub Totals in Pivot Table

vinieme12 — Thu, 09 Feb 2017 15:49:25 GMT

why not format it in the Number tab instead of Num()??

Re: Sub Totals in Pivot Table

vinieme12 — Thu, 09 Feb 2017 15:49:51 GMT

try

Num(sum(Aggr(SUM(Net),[Account Number],[Description]) ),'€#,##0.00')

Re: Sub Totals in Pivot Table

albertovarela — Thu, 09 Feb 2017 16:33:34 GMT

Hello,

No sure why you have duplicated records for same Account and Description but assuming that Sales and Amounts are the same this might work. See attached app

Re: Sub Totals in Pivot Table

Anonymous — Thu, 09 Feb 2017 16:45:39 GMT

Hi Vineeth

I have multiple currencies so I cannot format in number tab and the formula you gave is not giving expected result.It should give 12+12+2=14 but it is giving 26

Re: Sub Totals in Pivot Table

Anonymous — Thu, 09 Feb 2017 16:47:01 GMT

Hi Alberto

I am not unable to open your attachment as I am using Personal Edition.Data is like that they are not duplicated records

Re: Sub Totals in Pivot Table

albertovarela — Thu, 09 Feb 2017 16:50:59 GMT

Hope this helps

Re: Sub Totals in Pivot Table

Anonymous — Thu, 09 Feb 2017 18:10:12 GMT

Actually this is the problem I need 22+22 as 22 itself that I am getting but if I have 3 sites with sale as 22,22,12 it is giving null value instead of giving 22+12

Re: Sub Totals in Pivot Table

albertovarela — Thu, 09 Feb 2017 18:58:20 GMT

I used the data on your spreadsheet. I think it does what you are asking for...look at the CountryPlace site for Account ABCD1.

Re: Sub Totals in Pivot Table

Anonymous — Fri, 10 Feb 2017 06:25:05 GMT

Sorry for my confusion I am attaching a image according to my requirement

Re: Sub Totals in Pivot Table

Anil_Babu_Samineni — Fri, 10 Feb 2017 06:40:25 GMT

May be use DISTINCT Keyword for this

Re: Sub Totals in Pivot Table

Anonymous — Fri, 10 Feb 2017 07:24:33 GMT

Can you please explain where can I use it in my expression

Num(Aggr(SUM(Net),[Account Number],[Description]) ,'€#,##0.00'),

Re: Sub Totals in Pivot Table

vinieme12 — Fri, 10 Feb 2017 08:09:09 GMT

Like this?? vanivadlamudi

<<Modifications>>

SALES

=num(if(rowno()=0,Sum(aggr(avg(Sale),AccountNumber,Region)),sum(aggr(avg(Sale),AccountNumber,Desc))),'€ #,##0.00')

AMOUNT

=num(if(rowno()=0,Sum(aggr(avg(Amount),AccountNumber,Region)),sum(aggr(avg(Amount),AccountNumber,Desc))),'€ #,##0.00')

Re: Sub Totals in Pivot Table

Fri, 17 Feb 2017 16:28:46 GMT

try this

Num(Aggr(distinct SUM(Net),[Account Number],[Description]) ,'€#,##0.00'),

Re: Sub Totals in Pivot Table

Anonymous — Tue, 21 Feb 2017 07:05:45 GMT

It was also not working

Re: Sub Totals in Pivot Table

Anil_Babu_Samineni — Tue, 21 Feb 2017 07:16:04 GMT

Try simple Sum(DISTINCT Net)

And then check partial sum from presentation and then show me the image

Re: Sub Totals in Pivot Table

sasikanth — Tue, 21 Feb 2017 07:35:28 GMT

HI,

Try below Expression,

Num(Sum(AGGR(DISTINCT Sum(Net),[Account Number],Region,Country,City)),'€#,##0.00')

Re: Sub Totals in Pivot Table

Anonymous — Tue, 21 Feb 2017 10:17:37 GMT

Thank you Anil. It worked

but I used it like

Num(Sum(DISTINCT Aggr(SUM(Net),[Account Number],[Service Description]),'€#,##0.00')

Re: Sub Totals in Pivot Table

sasikanth — Tue, 21 Feb 2017 10:29:14 GMT

I don`t think simple distinct SUM will work if you have same net value for another combination. ( Region, Country , City, Account Number ), do check once .

Region='SOUTH' county='India', city='Delhi', AccNo=1011 , Net =100

Region='SOUTH' county='Aus', city='Sidny', AccNo=222 , Net =100

in this case SUM( distinct Net ) =100 instead of 200 ( as per my understanding)

Re: Sub Totals in Pivot Table

Anonymous — Fri, 24 Feb 2017 14:24:25 GMT

Ya Sasi

thats why I used AGGR function and I have mentioned it in my post