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

Goose306

Board Regular
Joined
Sep 26, 2014
Messages
52
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!
 

Musa15

Board Regular
Joined
Mar 8, 2015
Messages
63
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

Board Regular
Joined
Sep 26, 2014
Messages
52
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

Board Regular
Joined
Mar 8, 2015
Messages
63
Hi, Does it need to be a pivot table?

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

Goose306

Board Regular
Joined
Sep 26, 2014
Messages
52
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.
 
Last edited:

Forum statistics

Threads
1,082,269
Messages
5,364,148
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top