topic Calculating % In Pivot table in QlikView

Calculating % In Pivot table

Mon, 09 Jan 2017 13:15:02 GMT

Good afternoon, i am struggling getting the % calculated using the AGGR(NODISTINCT Function in my pivot table.

Our distribution can be by Sea or Air (more options in the future) and our target is to have the distribution 99% by sea, so ofcourse we want to see the % of each distribution for a certain period.

as seen in this table:

However the calculation to get our Yards invoiced is:

if(sum({<[Reference Number Qualifier] ={'CC'}>} replace([Reference Number],'.',',')*1),sum({<[Item Structure]={'FAB'} >} [Yards Invoiced]),0)

Somehow i am unable to calculate the % as seen in the table above using the AGGR(NoDistinct function, could anyone please help me with the logic of this calculation.

Re: Calculating % In Pivot table

sunny_talwar — Mon, 09 Jan 2017 13:21:15 GMT

May be try like this:

Sum(Aggr(if(sum({<[Reference Number Qualifier] ={'CC'}>} replace([Reference Number],'.',',')*1),sum({<[Item Structure]={'FAB'} >} [Yards Invoiced]),0), [Reporting Group], Distribution, [Mode of Transport]))

Sum(TOTAL <[Reporting Group], Distribution>Aggr(if(sum({<[Reference Number Qualifier] ={'CC'}>} replace([Reference Number],'.',',')*1),sum({<[Item Structure]={'FAB'} >} [Yards Invoiced]),0), [Reporting Group], Distribution, [Mode of Transport]))

Re: Calculating % In Pivot table

Mon, 09 Jan 2017 13:29:23 GMT

Thank you so much, it worked right away and i also found out what i did wrong.