+ Reply to Thread
Results 1 to 3 of 3

Forecasting expiry dates using a stacked bar graph /pivot table

  1. #1
    Registered User
    Join Date
    05-23-2020
    Location
    Reading, England
    MS-Off Ver
    Office 365 Pro Plus
    Posts
    2

    Forecasting expiry dates using a stacked bar graph /pivot table

    Hello,

    I have have four columns representing four different courses. Employees have to attend each course every 3 years. The data in the columns indicates when three years has passed since their last attendance.

    If the expiry date has passed the cell will display "OUTSTANDING".

    Some courses are not required for certain job roles so is represented by "NOT REQ".

    I want to forecast how many people are due to expire on a rolling 6 months lookahead. Ideally I'd like a bar chart with 6 stacked bars representing the number of expiry dates in that month. The stacking of the bar would be divided in four to represent the courses. e.g. There is high count of Course A expiry dates in September so I'll book in a higher amount of Course A's that month

    I assumed this would be straightforward through a pivot but cannot seem to get one to work.
    - Is it something to do with the mix of text and dates in the data?
    - Should I not be using a pivot and instead some sort of count function table? I attempted this but cannot seem to COUNTIF the dates based on their month & year simultaneously.


    I feel like I may be overthinking this as have been looking at this data for the last 3 days and seem to be blinkered.

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    47

    Re: Forecasting expiry dates using a stacked bar graph /pivot table

    Hi jamesclahar,

    Yes pivot tables can be tricky with different data types.
    I suggest a different approach in the attached file.
    On Sheet2, I simply convert your data in floating 30 day periods (not calendar month).
    On the Dashboard, we use COUNTIF to check how many courses are due to expire in the next 6 month (6 30-day periods to be more precise).
    This then can be displayed in a chart.

    Trust this gives you some ideas...
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-23-2020
    Location
    Reading, England
    MS-Off Ver
    Office 365 Pro Plus
    Posts
    2

    Re: Forecasting expiry dates using a stacked bar graph /pivot table

    Brilliant! Thank you, yes that's a looks a lot cleaner. Definitely a good idea to convert the data, was struggling to do anything with it in current form. I think I may be able to use the EDATE & TEXT functions to represent the rolling months as a month & year format. I'll need to have a thinkk.

    Thank you for your help again ORoos

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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