+ Reply to Thread
Results 1 to 6 of 6

Hires and Leaves per month dashboard

  1. #1
    Registered User
    Join Date
    10-16-2018
    Location
    Madrid
    MS-Off Ver
    Office 2013
    Posts
    3

    Hires and Leaves per month dashboard

    Hi everyone!

    I post a new issue that I have to build a dashboard with the information of the hires and leaves per month. It means, I need to have in the same dashboard two columns (one per hires and one per leaves), and I donīt know how can I handle it because the dashboard takes the hires leaves and count one leave if the employee has been terminated but not in the correct date.
    Let me attach an example.

    In the example, in January, there are two hires but the dashboard includes one leave, which actually doesnīt happen in January, but in march.

    Thanks to everyone!!!
    Regards,
    Guillermo
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Hires and Leaves per month dashboard

    This is a very good example of how "normalized" data makes analysis easy. Even though the original data structure is very simple, it requires special formulas to pull the information out.

    It is difficult to explain normalized data other than to say that it is generally better to go deep than it is to go wide. It works better if you go down with the dates than go across with them. For any given date, only two things can happen: a person can be hired or a person can leave. These are the variables. Employee Number and Date are the "constants" and Employee status is just "along for the ride" it is not used in figuring things out.

    I took the original data and normalized it into the blue-shaded table. I also made this an Excel table because:
    - Excel tables know how big they are, so formulas, charts and pivot tables based on them adjust automatically as rows are added or deleted. Pivot tables may need to be refreshed when the data changes.
    - Excel tables automatically copy down formulas, formats and validations

    So when you add an employee in column A directly below the table, it becomes part of the table. The formula in column B is copied down automatically and the validation for event is copied down automatically.

    The formula in column B is: =IF(COUNTIFS([Employee],[@Employee],[Event],"Leave")>0,"Inactive","Active") which is merely stating that if a person left, they are inactive. Note another nice feature of tables: you can use column headers in the formulas (and VBA too). It makes the formulas easier to understand and coding a lot easier.

    Here is more information on tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

    Once the data are in normalized form, they can be moved around in pivot tables in a number of outlines.

    The pivot table in Columns G:J is used to run the chart. The pivot table in Columns M:O is there to prove that you can get the original format back if you prefer to see it that way.

    Normalized data might seem counter-intuitive at first, but if you think of it as entering in things as they happen, it starts to make sense. I've seen cases where people have looked all over a grid for the correct box in which to put the "x" which is potentially an error-prone method of data entry.

    I also threw in a thing called a slicer. Slicers are fancy looking filters but they can do more than that. They can control multiple pivot tables at the same time. Notice that this slicer (Employee Status) is not part of either pivot table, but as long a an item is part of the data that makes up the pivot table, you can create a slicer for it. I recommend you do a web search on slicers. They are easy to set up and use. You should be a master of them in about 15 minutes.

    P.S. if you already have a large amount of data in the original format, I have a way of converting it into normalized data on a large scale. Let me know if you need to do this.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    10-16-2018
    Location
    Madrid
    MS-Off Ver
    Office 2013
    Posts
    3

    Re: Hires and Leaves per month dashboard

    Hello dflak,

    Amazing. Your solution is perfect. Actually, I have a large amount of data in the original format, so I would really appreciate if you can help me with that.

    Really thank you for your quick answer and amazing solution.

    Guillermo

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Hires and Leaves per month dashboard

    The attached workbook contains the conversion routine.

    The easiest way to use it is to remove the sample source data and copy and paste your data in there. On the Control Panel Sheet, you want to preserve columns A:B for your data setup. Then click the Normalize button. The normalized data will appear on the Normalized Data sheet. Copy and paste this to an new sheet in the workbook.

    I recommend that you convert this to an Excel table (see link in previous post). The last column will have the name "Value" as the header. Change this to Event.,

    Delete the slicer on Hoja1.

    Change the data source on both pivot tables to the new table.

    Replace the slicer if you wish.

    The process will destroy the formula in Column B for Active / Inactive. Copy / paste the formula from the worksheet I posted and then copy it all the way down the normalized data table. This is a one-time thing. Once each row has the same formula, new rows will pick it up automatically.



    Cut / paste the pivot tables and charts so they don't overwrite each other.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-16-2018
    Location
    Madrid
    MS-Off Ver
    Office 2013
    Posts
    3

    Re: Hires and Leaves per month dashboard

    Thank you very much!!! The macro works perfectly. Probably I will take the database normalized in a new spreadsheet because with the macro its quite heavy and it takes some time to change formats and so on, but it is great!

    Really appreciate your help!
    Guillermo

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Hires and Leaves per month dashboard

    Yes. The normalization spreadsheet is intended for one time use. Once you get the data the way you want it, copy it to the workbook where you will use it.

+ 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. [SOLVED] Lookup month when an employee leaves or starts a role
    By ergergfsd in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-29-2018, 12:22 AM
  2. Moving my Dashboard (with formulas) from Month to Month
    By carlhamill in forum Excel General
    Replies: 3
    Last Post: 08-17-2018, 11:09 AM
  3. Replies: 1
    Last Post: 04-18-2017, 10:19 PM
  4. How to count leaves for each date of the month
    By alipezu in forum Excel General
    Replies: 5
    Last Post: 03-11-2017, 01:49 PM
  5. Replies: 1
    Last Post: 07-31-2014, 01:43 AM
  6. [SOLVED] calculation for leaves paid but not more then days of month and not more then balance leav
    By dinesh4you in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2012, 04:27 AM
  7. 3 Month Moving Average Dashboard
    By DUKE888 in forum Excel General
    Replies: 2
    Last Post: 11-02-2011, 01:52 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