0

In my ODBC Excel File i have a pivot set up with 3 tiers: Bin,StockCode,Date. And NoOfDays as the value (always 1 per day/stockcode/bin combination) the 1st and 3rd tier should be summing totals and the 2nd tier should be reverting the Max. is this possible

This is for billing purposes on warehouse storage and it requires the hierarchy for drilling down but is should not consider each code as another value rather just report the maximum of all of them in the particular bin.

this is some of the detail:

Bin StockCode   SaveDate    NoOfDays
10001A  SANK1318    2019-03-10  1
10001A  SANK1320    2019-03-10  1
10001A  SANK1318    2019-03-11  1
10001A  SANK1320    2019-03-11  1
10001A  SANK1318    2019-03-12  1
10001A  SANK1320    2019-03-12  1
10001A  SANK1318    2019-03-13  1
10001A  SANK1320    2019-03-13  1
10001A  SANK1318    2019-03-14  1
10001A  SANK1320    2019-03-14  1
10001A  SANK1318    2019-03-15  1
10001A  SANK1320    2019-03-15  1
10001A  SANK1318    2019-03-16  1
10001A  SANK1320    2019-03-16  1
10001A  SANK1318    2019-03-17  1
10001A  SANK1320    2019-03-17  1
10001A  SANK1318    2019-03-18  1
10001A  SANK1320    2019-03-18  1
10001A  SANK1318    2019-03-19  1
10001A  SANK1320    2019-03-19  1
10001A  SANK1318    2019-03-20  1
10001A  SANK1320    2019-03-20  1
10001A  SANK1318    2019-03-21  1
10001A  SANK1320    2019-03-21  1
10001A  SANK1318    2019-03-22  1
10001A  SANK1320    2019-03-22  1
10001A  SANK1318    2019-03-23  1
10001A  SANK1318    2019-03-24  1
10001A  SANK1318    2019-03-25  1
10001A  SANK1313    2019-03-26  1
10001A  SANK1318    2019-03-26  1
10001A  SANK1313    2019-03-27  1
10001A  SANK1318    2019-03-27  1
10001A  SANK1313    2019-03-28  1
10001A  SANK1318    2019-03-28  1
10001A  SANK1313    2019-03-29  1
10001A  SANK1314    2019-03-29  1
10001A  SANK1318    2019-03-29  1
10001A  SANK1313    2019-03-30  1
10001A  SANK1314    2019-03-30  1
10001A  SANK1318    2019-03-30  1
10001A  SANK1313    2019-03-31  1
10001A  SANK1314    2019-03-31  1
10001A  SANK1318    2019-03-31  1
10001B  SANK1323    2019-03-10  1
10001B  SANK1323    2019-03-11  1
10001B  SANK1323    2019-03-12  1
10001B  SANK1323    2019-03-13  1
10001B  SANK1323    2019-03-14  1
10001B  SANK1323    2019-03-15  1
10001B  SANK1323    2019-03-16  1
10001B  SANK1323    2019-03-17  1
10001B  SANK1323    2019-03-18  1
10001B  SANK1323    2019-03-19  1
10001B  SANK1323    2019-03-20  1
10001B  SANK1323    2019-03-21  1
10001B  SANK1323    2019-03-22  1
10001B  SANK1323    2019-03-23  1
10001B  SANK1323    2019-03-24  1
10001B  SANK1323    2019-03-25  1
10001B  SANK1321    2019-03-26  1
10001B  SANK1323    2019-03-26  1
10001B  SANK1321    2019-03-27  1
10001B  SANK1323    2019-03-27  1
10001B  SANK1321    2019-03-28  1
10001B  SANK1323    2019-03-28  1
10001B  SANK1321    2019-03-29  1
10001B  SANK1323    2019-03-29  1
10001B  SANK1321    2019-03-30  1
10001B  SANK1323    2019-03-30  1
10001B  SANK1321    2019-03-31  1
10001B  SANK1323    2019-03-31  1

Below is what is currently displaying (without expanding to the date level)

Row Labels  Number Of Days
-10001A         44
  +SANK1313     6
  +SANK1314     3
  +SANK1318     22
  +SANK1320     13
-10001B         28
  +SANK1321     6
  +SANK1323     22

I require:

the Dates(not shown here) to sum on the Codes tier (i.e. 'SANK1313' should be 6 as per illustration) 
The Codes to Max on the Bin tier(i.e. '10001A' should return 22 and '10001B' should return 22)
the Bins to Sum in the grand total

The problem here is changing the Field value properties changes all value calculations for all tiers

Steven de Beer
  • 108
  • 1
  • 14
  • You might want to look at working with SQL, you have more control over the data, but you'll have to learn the language, albeit I believe it's not so hard to pick up the basics. – Jerry Apr 29 '19 at 15:30
  • Sorry for the late response. the problem is not SQL, the problem is Pivot in excel as per the tags to the post. i can handle SQL just fine. – Steven de Beer May 17 '19 at 12:55
  • No, I what I mean is, it's very likely that what you are looking for is not possible in Excel, but that you should be able to get what you are looking for if you use SQL, or perform Excel manipulations (i.e. not rely on Pivot Table only, but formulas, manual filtering, and such) – Jerry May 20 '19 at 05:10

0 Answers0