+ Reply to Thread
Results 1 to 7 of 7

Pivot table and chart all value shows in Sept

  1. #1
    Forum Contributor
    Join Date
    05-10-2019
    Location
    Penang, Malaysia
    MS-Off Ver
    2016
    Posts
    161

    Pivot table and chart all value shows in Sept

    Hi All,
    I have a worksheet which created to pivot table & chart. The issue is, all the value plotted to chart shows only Sept. What I wish to achieve is, the chart plotted correctly to monthly & quarterly.

    Data:
    1. RBA Awareness: 23-Sep-2020, 30-Sep-2020.
    2. Fire-Fighting Fire-Prevention (ERT): 28-Feb-2020.
    3. DISC 2020: 7-Mar-2020
    The count is correct nonetheless.

    Any help is greatly appreciated & please find attachment.

    Thank-You very much
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,159

    Re: Pivot table and chart all value shows in Sept

    As you are using the 2016 version of Excel you could use Get & Transform (Power Query) to produce the green table on Sheet 2 and then produce the pivot table and pivot chart from that table.
    1. Convert the range C1:K92 on Sheet 1 into a table (Ctrl + t)
    2. On the Data tab select From Range/Table
    3. In power query use the following Advanced Editor code:
    Please Login or Register  to view this content.
    4. After loading the table to a new sheet add the Quarter column using: =ROUNDUP(MONTH(C2)/3,0)
    5. Produce the pivot table with Quarter, Month and Date in the Rows area and Attribute in both the Columns and Values Area
    6. Produce the pivot chart as shown.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Contributor
    Join Date
    05-10-2019
    Location
    Penang, Malaysia
    MS-Off Ver
    2016
    Posts
    161

    Re: Pivot table and chart all value shows in Sept

    Hi JeteMC,

    Thanks for your response. However, when i copied the code to advance editor, ive got this error: Expression.Error: 5 arguments were passed to a function which expects between 2 and 4.
    Details:
    Pattern=
    Arguments=[List]

    Thank-You very much

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,159

    Re: Pivot table and chart all value shows in Sept

    Don't understand why that should happen, as it works in the copy of your file which is attached to post #2.
    Before pasting into advance editor did you delete the code that was already there?
    If not try that, and if so then please upload the file which is producing the error message.
    Let us know if you have any questions.

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    5,195

    Re: Pivot table and chart all value shows in Sept

    In 2016 and prior, AddIndexColumn only takes 4 arguments. The fifth one (type) was added later.
    Rory
    I drink, and I know things

  6. #6
    Forum Contributor
    Join Date
    05-10-2019
    Location
    Penang, Malaysia
    MS-Off Ver
    2016
    Posts
    161

    Re: Pivot table and chart all value shows in Sept

    Hi JeteMC, royra..

    I've switched to Excel 2019 from another PC & its working as expected. Looks like Excel 2016 couldnt support for >5 arguments as rorya mentioned.
    Reputations issued to you both & will mark this thread to Solved.
    Thank-You very much
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,159

    Re: Pivot table and chart all value shows in Sept

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. Replies: 1
    Last Post: 10-16-2019, 01:23 PM
  2. Pivot Chart shows date format as number
    By promo_dobbu in forum Office 365
    Replies: 1
    Last Post: 10-10-2019, 10:21 PM
  3. [SOLVED] Pivot Table shows same month twice!
    By Erik199 in forum Excel Charting & Pivots
    Replies: 18
    Last Post: 06-07-2018, 07:16 AM
  4. Pivot Chart that shows average number between dates.
    By B_Jarbs in forum Excel General
    Replies: 3
    Last Post: 02-07-2016, 11:09 AM
  5. SQL Pivot table shows percentages as date and time
    By mhf89 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-19-2015, 09:09 PM
  6. [SOLVED] The last row of a pivot table shows blanks, but there is data.
    By D May in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-25-2006, 08:35 AM
  7. Pivot Chart which shows average line with below and above figures
    By Env shah in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-03-2006, 06:55 AM

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