Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
We value your feedback – take our 5-minute QlikView modernization survey
cancel
Showing results for 
Search instead for 
Did you mean: 
danielle_v
Creator
Creator

AGGR in Pivot Table

Hello,

I currently have a pivot table like the below, which shows the allocated/contracted volume % splits at country/destination/carrier level vs. the actual shipped volume and percentages.  COUNTRY, DESTINATION, CARRIER & ALLOCATION are dimensions. Shipped Volume, Actual % & Planned Volume are expressions. The expression for Planned Volume is as follows;

(sum({$<
AllocationShipment_Week = {$(vAllocationWeek)}>}
total <COUNTRY, DESTINATION>
ShippedVolume) )* [ALLOCATION]

 

COUNTRYDESTINATIONCARRIERALLOCATIONShipped VolumeActual %Planned Volume
ChinaFCarrier A25%218%2.75
ChinaFCarrier B25%00%2.75
ChinaFCarrier C50%982%5.5
ChinaTCarrier A25%934%6.6875
ChinaTCarrier B25%00%6.6875
ChinaTCarrier C50%17.7566%13.375
IndiaFCarrier A30%3133%28.125
IndiaFCarrier B30%29.2531%28.125
IndiaFCarrier C40%33.536%37.5
IndiaTCarrier A30%00%2.55
IndiaTCarrier B30%2.2526%2.55
IndiaTCarrier C40%6.2574%3.4
USAFCarrier A50%16.75100%8.375
USAFCarrier D50%00%8.375
USATCarrier A50%2100%2
USATCarrier D50%00%0
AustraliaFCarrier A40%25.537%27.3
AustraliaFCarrier B30%6.7510%20.475
AustraliaFCarrier C30%3653%20.475
   Total227 227

 

This works absolutely fine, but I also need an additional pivot table like the below - so I somehow need to calculate the Planned Volume at COUNTRY, DESTINATION & CARRIER level, but only display it at CARRIER level.

Can this be done with aggr? I have tried numerous variations but I'm going round in circles and not getting anywhere!

 

CARRIERShipped VolumeActual %Planned Volume
Carrier A86.2538%77.7875
Carrier B38.2517%60.5875
Carrier C102.545%80.25
Carrier D00%8.375
Total227 227

 

Any help greatly appreciated!!

Labels (5)
1 Solution

Accepted Solutions
sunny_talwar

How about this?

Sum(Aggr(
  (Sum({$<AllocationShipment_Week = {$(vAllocationWeek)}>}total <COUNTRY, DESTINATION> ShippedVolume))* [ALLOCATION]
, COUNTRY, DESTINATION, CARRIER))

View solution in original post

2 Replies
sunny_talwar

How about this?

Sum(Aggr(
  (Sum({$<AllocationShipment_Week = {$(vAllocationWeek)}>}total <COUNTRY, DESTINATION> ShippedVolume))* [ALLOCATION]
, COUNTRY, DESTINATION, CARRIER))
danielle_v
Creator
Creator
Author

Thank you so much - that works perfectly!