Trying to do some custom Pivot Table summaries, wondering if this is possible...

[Goose306]

Hi all,

Pretty straightforward question, I hope. I have a data set that I'd like to see if I can get set up in a pivot table in a custom way. The data is being pulled out a SQL database and looks like this when I retrieve it (dummy data created for example purposes - its actually quite a bit larger with more dates/categories):

What I would like know if it's possible to do is summarize the data where date data in both the column fields and row fields. In the column fields, the data is summarized as a week, whereas in the row field it would be summarized as day of week. Approximate mocked up example (because I can't do what I want in Pivot Table, or I wouldn't be here )

Where the $xx.xx would be summarized amount.

I know how to create the summarized columns, but I'm a bit stumped on the "Day of Week" row fields. Essentially, I need a calculated field, but I need it in the Rows column.

I've played around with it a bit, and my questions are:

• Is this possible with my dataset as-is? Even if it takes quite a bit of hand-work (it'll be pushed to VBA whenever I get it down, so that is not a concern.)
  • If it can't be done by hand, is there a way to override some of those controls with VBA and get it to build (as there is with several other functions where you can work with the data directly - I'm a relatively experienced VBA developer, just haven't worked with pivots much, so assuming it isn't too complicated this wouldn't be an issue.)
• If it isn't possible with the dataset, is there any options I would have beyond directly transposing the data coming from my SQL database? I could feasibly change it from a wide table to a tall table (transpose it back, to where each spend item is an individual line with a date column). I'm trying to avoid this if at all possible as it will be costly when looking at other operations I'm doing with the data (data is >100k rows when transposed to tall with 9 columns and I'm running a lot of other VBA to build tables and retrieve other data from it beyond just the pivot).

Thanks for any help you can provide!

 

[Goose306]

Bump

 

[Musa15]

Hi, Can you reformat columns D-M to date Format "Wednesday MM/DD/YY" ? This would be your first step, and perhaps a formula that sums by week for your data summary.

 

[Goose306]

Hi, Can you reformat columns D-M to date Format "Wednesday MM/DD/YY" ? This would be your first step, and perhaps a formula that sums by week for your data summary.

I can certainly apply that format (I can also just control for day of week manually in VB), the issue is trying to get the dates to appear as a row field as well as a value field. When I try to create a Calculated Item, it throws fits because I'm selecting multiple items within each category, and I can't use a Calculated Category in a Row Field.

 

[Musa15]

Hi, Does it need to be a pivot table?

I was imagining a sum if formula with the date parameters.

 

[Goose306]

Hi, Does it need to be a pivot table?

I was imagining a sum if formula with the date parameters.

In this case, yes, unless I wanted to try to replicate the functionality of the pivot table using VBA (which is a "Why reinvent the wheel?" type question). The end goal:

• I have ~20k rows of category that have potential spend in them. Each of these catagories has spend data for a date, assuming there is spend (if no spend, cell is blank). I'm currently pulling a rolling month from the SQL database, although that may increase up to 60 days. Thus, this is a pretty hefty dataset for Excel (up to around 1.2 mil datapoints).
  • A user can open a userform which asks how they would like to query this data, and how they want the hierarchical order of data. There is around 12 descriptive categories currently that can be retrieved from this data, one of them being "Days of Week" (the one giving me pains). Up to 3 selections are allowed for categories.
  • If a user selects two categories, custom tables are built that hold the data. Data is retrieved by long chains of SUMIFS that essentially query off of the large table, load the data into an array, sort it using various algorithms, then parse it back out to the user in a formatted table. (EX: They may select one category, but then can select an individual item within that category to drill down to that particular data set).
  • If a user selects 3 categories, the idea is that it would instead build custom pivot tables, rather than general tables. The reason for that is frankly complexity of data and coding - once you start getting optional, dependent subcategories it spirals quite quickly into 3 and 4d arrays holding potentially hundreds of thousands of points of data, and its an issue.

Which is where we are at now - The data is delivered in a week over week format as the header (or data fields in pivot tables) with dependent data being delivered in rows (or row fields, in the case of pivot tables). The issue is, I need to allow users to select "Day of Week" as a category, and have that go in the row fields - that way they can see a breakdown of the spend for each day of the week, week over week, if that makes sense.

Ultimately the question is whether I just need to restructure my data (which will make the general table building part much slower) or if the current method will work. If I have to restructure the data from my SQL source, I may just push it all to pivot tables (I'd just prefer not to, as format control on plugging in raw data into cells is a lot better than trying to force pivots to move the way you want to!)

EDIT: Additionally, moving everything to pivots would mean I would lose the ability to do my custom sorting algorithms, something I'd really quite prefer not to.