Pivot table - weekdays/weekends & Mondays

[JamesonMH]

Hi all,

I was trying to summarize just a few hundred rows of annual data today in a simple 3 bar column pivot chart. The main things happening were i) date and ii) # of customers seen by business on those dates.

My requestor was looking for 3 specific things in the same pivot chart:

a) total customers seen on weekdays,
b) total customers seen on weekends, and
c) total customers seen on Mondays only

I naturally used a PT, but the problem was the sum of a) b) & c) above was greater than the data set itself - since Mondays are included in "a)" and "c)" above.

Question is, how do you create such a pivot table when your total on the pivot table exceeds the total within the data set??

I tried tirelessly to slice & dice the pivot table to get all 3 items above included, but it wasn't working. I had to resort to copying and pasting the "Monday only" rows into the same data set so the pivot table would recognize them as unique items.

Thanks for your time.

James

 

[Fazza]

I think, James, it is an awkward requirement. And the solution is either (a) to add another set of data to worksheet source data, as you've done, or (b) do the equivalent thing as a modification to the dataset via SQL. This second approach would not be transparent in that the data visible on the worksheet would not appear to match the pivot table's result. So the SQL could be like

SELECT fields of data, 'BaseData' AS [DataCategory]
FROM source
UNION ALL
SELECT fields of data, 'Added'
FROM source
WHERE datefield = 'Monday'

And the actual criteria for Monday might be a MOD function or simple text comparison of formatted dates.
I edited the SQL to add another field along the way to specifically tag the added records.

 

[JamesonMH]

I think, James, it is an awkward requirement. And the solution is either (a) to add another set of data to worksheet source data, as you've done, or (b) do the equivalent thing as a modification to the dataset via SQL. This second approach would not be transparent in that the data visible on the worksheet would not appear to match the pivot table's result. So the SQL could be like

SELECT fields of data, 'BaseData' AS [DataCategory]
FROM source
UNION ALL
SELECT fields of data, 'Added'
FROM source
WHERE datefield = 'Monday'

And the actual criteria for Monday might be a MOD function or simple text comparison of formatted dates.
I edited the SQL to add another field along the way to specifically tag the added records.

Thanks for your reply Fazza - I appreciate your time.

Wow, that 2nd alternative of yours is definitely beyond my skillset. I was hoping to get a reply from someone out there stating something relatively obvious that I just missed.

It's interesting that a pivot table can't slice & dice the data in such a way. This request I received (which spurred this post) wasn't from a hard-core data/Excel user - it was just a general business request that seemed pretty straightforward on first glance...

So I guess the takeaway here is that a pivot table will only display data within it to the extent that the sum of data displayed doesn't exceed the data set total?

James

 

[SpillerBD]

My requestor was looking for 3 specific things in the same pivot chart:

a) total customers seen on weekdays,
b) total customers seen on weekends, and
c) total customers seen on Mondays only

I naturally used a PT, but the problem was the sum of a) b) & c) above was greater than the data set itself - since Mondays are included in "a)" and "c)" above.

Question is, how do you create such a pivot table when your total on the pivot table exceeds the total within the data set??

Its either going to PowerPivot and leveraging DAX or using some additional calculations in the original DataTable.
The calculations would be Array calculations which could quickly be overwhelming for any machine, which is where PowerPivot has a huge advantage.

Though I doubt it, while adding the source Table to the DataModel and then building a PivotTable using the DataModel as the source does give you the added function DISTINCTCOUNT, the solution you want likely still needs a little more....

 

[Fazza]

Its either going to PowerPivot and leveraging DAX or using some additional calculations in the original DataTable.

FYI there is another way, as I wrote above. Doesn't need PowerPivot/DAX, additional calculations, adding data anywhere, nor VBA. Works with any version of pivot tables too.