+ Reply to Thread
Results 1 to 7 of 7

Trends by date in pivot tables

  1. #1
    Registered User
    Join Date
    01-04-2021
    Location
    Anchorage, AK
    MS-Off Ver
    Latest 365
    Posts
    4

    Trends by date in pivot tables

    This seems like it should be a simple request, but I cannot get pivot tables and the resulting charts to handle it.

    I have a log of events with date stamps. I simply want to show the number of events by day over a date range, including dates with no data (to properly show a trend).

    I created a pivot table, with the date-time stamp as the row. Excel groups them into days (good), but with the format "23-Nov, 24-Nov, etc.", and if the date stamps cross a year, the next year's dates (1-Jan, 2-Jan, etc.) don't sort properly (they appear first in the sort). If I try to add year to the grouping, Excel crashes. This is on the latest Office 365.

    There has to be a simple way to do this, I can't be the first to have this need.

    Thanks for your help.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Trends by date in pivot tables

    Can you share a sample file that demonstrates the problem? I grabbed a random data set from the forum and had no trouble grouping the resulting pivot table by day and year. At 17000 records, there was a short delay between changes before Excel finished the calculations, but nothing else to indicate any problems.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    01-04-2021
    Location
    Anchorage, AK
    MS-Off Ver
    Latest 365
    Posts
    4

    Re: Trends by date in pivot tables

    Yes, here you go (I couldn't attach it for some reason). Thanks!
    https : / / drive.google.com/file/d/1ddEAkNYGRaqV4ihpjgHUKtx6kSTktT1u/view?usp=sharing

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Trends by date in pivot tables

    If I try to add year to the grouping, Excel crashes.
    Does it actually crash, or just take a long time (longer than you are willing to wait) to calculate? From my limited testing, it seems more like "taking a long time to calculate (and I don't want to wait that long)" than "excel crashes".

    Upon first opening your file, I notice that -- where the OP talks only about grouping by year and day -- your pivot table is configure to group by day, hour, minute, and second. I notice that I have no trouble if I drop the time groupings and only have it group by year and day (it also has no trouble grouping by year, quarter, month, and day). I don't understand the internal nuts and bolts, but it appears to me that trying to group by year, day, and time division requires more calculation time than you or I are willing to wait.

    Solution probably depends on exactly what you need from this. As I noted, I had no trouble grouping by year and day (without all the time groupings). If you don't need the time groupings, then simply group by year and day. If you do need the time groupings, then we will need to dive deeper into this or determine how much time is needed to let Excel finish its calculations (or consider whether a different pivot/database utility will work better).

  5. #5
    Registered User
    Join Date
    01-04-2021
    Location
    Anchorage, AK
    MS-Off Ver
    Latest 365
    Posts
    4

    Re: Trends by date in pivot tables

    Yes, it crashes. It freezes on my powerful machine for 30+ minutes before I finally kill it. With <50 rows, that is a crash.

    I took out the groupings like you suggested, and just added year. That seemed to help. Thanks

  6. #6
    Forum Contributor
    Join Date
    10-14-2020
    Location
    Shanghai
    MS-Off Ver
    O365
    Posts
    156

    Re: Trends by date in pivot tables

    Another thing I noticed is you can load the data into Powerquery and prepare them to ensure a faster loading
    The issue for the crash in your computer is the seconds in the time grouping. I am not sure why but as soon as i group by year and month, i see the whole 2020, every day. This means a group by second will give you much more than 100k rows in your pivot table which cause the crash

  7. #7
    Registered User
    Join Date
    01-04-2021
    Location
    Anchorage, AK
    MS-Off Ver
    Latest 365
    Posts
    4

    Re: Trends by date in pivot tables

    Excellent point hansolu

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Using Pivot Tables with 2 Different Date Variables x 2
    By 2shlee in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-21-2016, 06:21 AM
  2. Date format in Pivot Tables
    By MaximusXL in forum Excel Charting & Pivots
    Replies: 15
    Last Post: 03-10-2014, 10:27 AM
  3. VBA to update pivot tables date between value - Date error
    By delboy2405 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-11-2013, 01:46 PM
  4. Pivot Tables should use the year of an date
    By flambert in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 09-03-2013, 09:55 AM
  5. Date formats in pivot tables using power pivot in Excel 2010.
    By myobreportguru in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 03-25-2013, 10:21 PM
  6. Date formatting for Pivot tables
    By MattGriff in forum Excel General
    Replies: 2
    Last Post: 03-06-2009, 07:44 AM
  7. Using Date Grouping in Pivot Tables
    By RaoulDuke in forum Excel General
    Replies: 1
    Last Post: 05-09-2005, 01:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1