Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]
COUNTRY | DESTINATION | CARRIER | ALLOCATION | Shipped Volume | Actual % | Planned Volume |
China | F | Carrier A | 25% | 2 | 18% | 2.75 |
China | F | Carrier B | 25% | 0 | 0% | 2.75 |
China | F | Carrier C | 50% | 9 | 82% | 5.5 |
China | T | Carrier A | 25% | 9 | 34% | 6.6875 |
China | T | Carrier B | 25% | 0 | 0% | 6.6875 |
China | T | Carrier C | 50% | 17.75 | 66% | 13.375 |
India | F | Carrier A | 30% | 31 | 33% | 28.125 |
India | F | Carrier B | 30% | 29.25 | 31% | 28.125 |
India | F | Carrier C | 40% | 33.5 | 36% | 37.5 |
India | T | Carrier A | 30% | 0 | 0% | 2.55 |
India | T | Carrier B | 30% | 2.25 | 26% | 2.55 |
India | T | Carrier C | 40% | 6.25 | 74% | 3.4 |
USA | F | Carrier A | 50% | 16.75 | 100% | 8.375 |
USA | F | Carrier D | 50% | 0 | 0% | 8.375 |
USA | T | Carrier A | 50% | 2 | 100% | 2 |
USA | T | Carrier D | 50% | 0 | 0% | 0 |
Australia | F | Carrier A | 40% | 25.5 | 37% | 27.3 |
Australia | F | Carrier B | 30% | 6.75 | 10% | 20.475 |
Australia | F | Carrier C | 30% | 36 | 53% | 20.475 |
Total | 227 | 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!
CARRIER | Shipped Volume | Actual % | Planned Volume |
Carrier A | 86.25 | 38% | 77.7875 |
Carrier B | 38.25 | 17% | 60.5875 |
Carrier C | 102.5 | 45% | 80.25 |
Carrier D | 0 | 0% | 8.375 |
Total | 227 | 227 |
Any help greatly appreciated!!
How about this?
Sum(Aggr(
(Sum({$<AllocationShipment_Week = {$(vAllocationWeek)}>}total <COUNTRY, DESTINATION> ShippedVolume))* [ALLOCATION]
, COUNTRY, DESTINATION, CARRIER))
How about this?
Sum(Aggr(
(Sum({$<AllocationShipment_Week = {$(vAllocationWeek)}>}total <COUNTRY, DESTINATION> ShippedVolume))* [ALLOCATION]
, COUNTRY, DESTINATION, CARRIER))
Thank you so much - that works perfectly!