+ Reply to Thread
Results 1 to 12 of 12

Line Chart Which Shows The Sales In The Selectable Periods

  1. #1
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    2010
    Posts
    862

    Line Chart Which Shows The Sales In The Selectable Periods

    + Column A has Sale Dates

    + Column B has Sale Amounts of a product.


    This table will be updated regularly by the new sale records.

    I want to create a line chart which updates itself when the new records are entered.

    I want that chart is controllable maybe with a slider.

    For example I choose 3 months It shows the sales amount in the last 3 months.
    Or I choose last 9 months and 9 months.

    How can I do this?
    Attached Files Attached Files

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

    Re: Line Chart Which Shows The Sales In The Selectable Periods

    You have not shared what you have tried, so I don't know if you've already tried and rejected something like this, but here's how I would do it:

    1) Select A1:B1000 (or however large a range you will ever need) and insert a line chart. Format the line chart the way I want it. In particular, if I place it in the space to the right of the table, make sure to bring up the "size and properties" attributes of the chart and set it to "don't move or size with cells".
    2) To view a specified date range, use the autofilter drop down on the date column and specify to filter by the desired date values. For the last three months (current month is september), I could filter by dates after 1 June.
    3) To add data, simply add it, and it should add to the chart without further input.

    Will something like that work for you, or have you already rejected that kind of approach?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    2010
    Posts
    862

    Re: Line Chart Which Shows The Sales In The Selectable Periods

    Sorry for not sharing the file previously.


    1) On the date column I can only filter by a specific month. (not periods like last 3 months, last 9 months etc.)


    2) How can we set the periods (For example I want it looks monthly here like june, july etc.) on the x axis values for selected period (for example last 3 months?



    3) Can we put a slider to make the chart show the desired period? (last 3 months, last 6 months etc.)
    Attached Files Attached Files

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

    Re: Line Chart Which Shows The Sales In The Selectable Periods

    1) Excel 2007 and 2010 seem pretty similar, so I am not sure why your version of Excel is only allowing you to filter by a specific month and not by a range of months/dates. I have no trouble filtering by "last n months". Drop down on Date field -> Date Filters -> After -> Pick an appropriate date in June to get the last 3 months. Make sure you are looking in the right place for all of the filtering options. If your copy of Excel is not allowing you to use those filter options, then you may need to look at your installation and figure out why these options are disabled or broken.

    2) I don't understand this question. Are you wanting your chart to show monthly data rather than daily data? If so, select the horizontal axis -> format axis -> change base unit to months.

    3) I'm generally optimistic that anything is possible with enough time, effort, and ingenuity. A slider would probably require VBA and is beyond my skill set. But I'm sure several others on the board could help you put a slider on.

  5. #5
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    2010
    Posts
    862

    Re: Line Chart Which Shows The Sales In The Selectable Periods

    Currently I'm using Excel 2013. I'll add to my profile it. Now I use Excel 2010 on my other PC.

    1) Do we filter here? (See the picture I shared please.)




    2) Yes It make the data show in months. However horizontal axis values are still the same date format dd.mm.yyyy.
    Can we do it only mm?

    3) If we do pivot chart rather than regular chart does a slider work?
    Attached Images Attached Images

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,448

    Re: Line Chart Which Shows The Sales In The Selectable Periods

    1) That is the filter menu. In the middle of the "red" box is a "Date Filters" submenu. Click on it to bring up the submenu, and you should get a long list of different date filter options -- including an "After" option that can be used for filtering the last three months.

    2) Yes. Select the axis -> Format axis -> Number -> choose a suitable month only format or enter the custom format code "mm".

    3) Slider or Slicer?
    My version of Excel doesn't support slicers (or implements them very differently, depending on exactly what you mean by slicer), but certainly slicers can be part of a pivot table solution in your, newer versions of Excel. I probably don't know how to use them well enough to help you implement a pivot table with slicer, but others here should be able to help with that.
    If you meant slider, I am not aware of any built in way of using a slider with a pivot table/chart. Again, I'm sure it can be done with VBA if one has the knowledge to write the code for it.

  7. #7
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    2010
    Posts
    862

    Re: Line Chart Which Shows The Sales In The Selectable Periods

    1) Can we write a formula at the Date Filters - Custom Filter section which shows the sales of the last 3 months from today? How?

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,254

    Re: Line Chart Which Shows The Sales In The Selectable Periods

    The only input that seems to be allowable in the date filter > custom filter is a date, not a formula.
    In the attached formula based proposal the following formulas list the dates beginning with the date 3 months ago up until today:
    In cell D2: =DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY()))
    In cells D3:D102: =IF(D2<H$1,SUM(D2,1),"")
    Note that today's date is placed in cell H1 using: =TODAY()
    The values are listed in E2:E102 using: =INDEX(B$2:B$102,MATCH(D2,A$2:A$102,0))
    The chart axis is set up as per MrShorty's recommendation.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    2010
    Posts
    862

    Re: Line Chart Which Shows The Sales In The Selectable Periods

    What are the values on the columns D and E?

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,254

    Re: Line Chart Which Shows The Sales In The Selectable Periods

    As displayed in the file attached to post #8 the values in column D are dates within the last three months of today and the values displayed in column E are counting numbers.
    Let us know if you have any questions.

  11. #11
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    2010
    Posts
    862

    Re: Line Chart Which Shows The Sales In The Selectable Periods

    ı think Pivot Chart with a time slicer is better solution for this.

    Thank you very much for the answers.

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,254

    Re: Line Chart Which Shows The Sales In The Selectable Periods

    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: 0
    Last Post: 07-02-2019, 06:30 AM
  2. Creating a line chart from a sales graph
    By dan76n in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-15-2019, 07:33 AM
  3. Line chart values shows deviation from real data
    By ssshnitco in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-07-2013, 04:32 PM
  4. Creat a line chart of monthly sales
    By Legend Rubber in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 05-28-2013, 08:48 AM
  5. Drawing line chart with irregular time periods
    By JustinF in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-10-2008, 04:56 AM
  6. [SOLVED] 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
  7. [SOLVED] How do I make a line non-selectable?
    By Shinka in forum Excel General
    Replies: 4
    Last Post: 10-05-2005, 05:05 PM

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