Pivot Table Question

Copper Contributor

Update: Just added example file 2:21 EST

 

I have a data set of about 12 Columns and 4100 Rows with a "Billing Cycle Date" Column formatted to date format d/mm/yyyy (not that that matters).  Source data contains data for 2020 and (now) 2021. I'm targeting data only for DEC 2020, JAN 2021 and FEB 2021.  All Billing Cycle Date Cells are populated with dates formatted as: "2/23/2021", "1/23/2021" or "12/23/2020" and each record is grouped together by their respective dates.  

 

I've filtered and grouped by every means thinkable 2) I copied source data to a new sheet / deleted the old sheet - redefined the range 3) I've deleted "vacant" cells at the bottom of the data set.  Etc.  The Date filters don't seem to want to Display FEB data either... UNLESS I move Feb data to a separate spreadsheet and Pivot Table on that month alone... (?)

 

No matter what I've tried, I can't get the latest and most recent 2/23/2021 data to appear in the Pivot Table to where the it displays all three month's summarized data (just DEC and JAN).  When I do fumble around with groupings enough, I can eventually get all three month names to appear in the table but February's data in the Pivot Table is blank (even through the entire February data set is populated in the source sheet).  Any thoughts as to why this is happening?

 

DEC

JAN

FEB

 

Ex:

 

Number.          Bill Cycle.  Cost.    Etc...  --->

123-098-7891 12/23/20  $39.00.  xxx

567-456-7687 12/23/20  $39.00.  xxx

321-678-7456 12/23/20  $39.00   xxx

123-456-7891 1/23/21    $39.00

678-456-7687 1/23/21    $39.00

321-456-7456 1/23/21    $39.00

123-456-7891 2/23/21    $39.00   Etc

678-456-7687 2/23/21    $39.00   Etc

321-456-7456 2/23/21    $39.00

8 Replies

@MikeM Difficult to diagnose without seeing your file. Can you upload a sample file with say a hundred rows of data spanning, say November 2020 through February 2021. Remove any confidential information, but leave the dates as you have them in your actual file.

Ok yes standby
Thanks Riny_van_Eekelen- Just added example file 2:21 EST

@MikeM Not sure I understand. You indicate that you expect to see three months for the first item in the PT. When I filter cost centre 12345 for USER 1, I find only one entry for 23 March 2020. Why do you expect to see the months of Dec 2020 through Feb 2021? 

 

I recreated a PT resembling yours, filtering dates (right-click on a month row header and select "date filter) for 1 Dec 2020 through 28 Feb 2021. It doesn't seem to be a very meaningful PT to me, but perhaps it is to you. Let me know if this is what you had in mind. See attached (MASTER DATA). But perhaps the PT in the other file makes more sense. 

Thanks again Riny_van_Eekelen: Target consecutive months are 12/23/20, 1/23/21 and 2/23/21 (past three months). There is data for all three months within the data set. Here's the goal: "Who hasn't used their mobile phone within the last three CONSECUTIVE months by cost center#, device and name?" Have a look at "REV 2" where I placed some screen shots from an older report I got working (somehow...). Names are blotched out in red.

@MikeM 

Perhaps you may use this setting for dates field

image.png

and show zero for empty in PivotTable settings

image.png

Sergei Baklan: I will try this thank you.
@Sergei Baklan thanks. I discovered a space after each of the cost center numbers (in the original worksheet but not in the one uploaded) so that may have caused some of the problem. The table appears to be behaving more as expected after correcting that and refreshing. Thank you and again @Riny_van_Eekelen.