topic Problem in deriving Aggregated sum based on a Field in QlikView

Problem in deriving Aggregated sum based on a Field

Anonymous — Fri, 22 Nov 2013 08:25:05 GMT

Hi. Good Afternoon.

Before going forward with the problem, please refer to the situation where I am representing the following data in a Pivot table:

Occupancy in % = sum([Bin Occupancy]) of Plant / SUM([Bin Occupancy]) of 'Warehouse' * 100

** where [Bin Occupancy] is a calculated value

I am unable to get the Aggregated sum based on the 'Warehouse' with the below expression. Tried several ways but the problem persists.

=sum([Bin Occupancy]) / aggr(([Bin Occupancy]), Warehouse) * 100

Please assist.

Thank You.

Regards,

Vijit

Fri, 22 Nov 2013 08:30:35 GMT

Hi,

try like this.

(aggr(sum([Bin Occupancy]),Plant) / aggr(sum([Bin Occupancy]), Warehouse) ) * 100

if this will not helpful for your then please attach sample file here.

Thanks,

Ashutosh

Fri, 22 Nov 2013 08:31:44 GMT

Hi Vijit,

Did you gave a try using Total as below?

Occupancy in % = sum([Bin Occupancy]) of Plant / SUM(Total [Bin Occupancy]) of 'Warehouse' * 100

=sum([Bin Occupancy]) / aggr(Total ([Bin Occupancy]), Warehouse) * 100

Regards,

Anonymous — Fri, 22 Nov 2013 08:51:28 GMT

Hi.

Either case, it's not functioning. Please also make a note that Warehouse has two values = 'K01' and 'B01'

Regards,

Vijit

Anonymous — Fri, 22 Nov 2013 09:04:32 GMT

Hi.

Even after using dimentionality() function, I found a simple solution:

= Sum([Bin Occupancy])/Sum(Total <[Warehouse]> [Bin Occupancy])

Regards,

Vijit