Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Guys,
    The attached export example has a listing of company travel records. I am trying to turning this into a number of different reports. I've transformed some of the columns provided in the export (like TRAVEL DATES) into more usable data (with your help) What is the best way to set this data table up to create charts that would show how many people are gone to a given country on an given day trended overtime? Can a pivot table be set up that way?

    Perhaps the second aspect of this is the difficulty of making sure the same person isn't counted more than once. It looks like someone might be in one location for a certain duration, but circumstances changed and a new travel record gets created without necessarily modifying the old one with a new end date. Such a thing happens with DOE JANE Q. She is in the United Kingdom 6-21 Oct but then seems to go to Germany from 13-24 Oct. I imagine that probably can't be accounted for in Excel, but would have to be queried out and scripted in Access.

    Any help is always appreciated
    Amy
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Is something like this what you are after?

    Steve

    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Steve,
    I think this is close to what I was thinking. However, I am confused about the y-axis scaling (they should be whole numbers) and surprised that it is a flat trend over the total travel period--Oct 3 (earliest start) to Dec 31 (latest end date).

    I'd like to be able to show how many travelers are actually gone each day of the total travel period. The day after the end date for someone's travel they shouldn't be counted as gone.

    It seems the pivot table is the way to go, but I need help tweaking it.

    Thanks
    Amy

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you only want whole numbers on the Y-Axis, dbl-click it and set the major unit to 1 and it will only display whole days.

    The trend displays only the periods when people are gone and it seems that for some cities only one person goes in a particular time period.

    Overlap in time-periods can not be captured with the data in a pivot table since the time periods are defined by your start and end dates. To get it out by individual dates with a pivot would require that each individual date be listed and you capture who is where [The data would be columns of date, country, City, person with many dates duplicated and a long list.

    From your data set you could extract a table of dates with the number in each country on those dates. The attached has that. I created a chart with all the countries on one chart (I don't like it with so many countries, this type would work well for at most 3 countries). I also did an extract of each country which can be plotted with a combobox to choose all the countries or each individual one.

    Perhaps this is more in line with what you want...

    Steve

    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Steve,
    You nailed it. This works great. I was hoping it could be done without necessarily created another table, but I can see how that is unavoidable.

    The SUMPRODUCT formulas accommodate multiple IF statements I guess. I was thinking of something like using COUNTIF the travel day is equal to or greater than the Start Date and equal to and less than the End Date. Since COUNTIF can't use multiple parameters the SUMPRODUCT must be used. Is that right?

    I created a third chart.that shows the combined totals.

    Question: Can this be interfaced with MS Access and scripted for automation? I am working an MS Access solution that merges data exports from multiple travel databases. It does some of the transformation steps that you saw in the provided export, some data enrichment (where countries are grouped for regional reporting and employee type and travel costs are added to the export table) and record deduping. I receive the data exports on a weekly basis, so the deduping becomes necessary. Is it optimal to use the two solutions together--MS Access for the data management and then updated this MS Excel workbook for the charts? Your advice is greatly appreciated.

    Thanks
    Amy
    Attached Files Attached Files

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The SUMPRODUCT formulas accommodate multiple IF statements I guess. I was thinking of something like using COUNTIF the travel day is equal to or greater than the Start Date and equal to and less than the End Date. Since COUNTIF can't use multiple parameters the SUMPRODUCT must be used. Is that right?
    Yes, SUMPRODUCT can act like multiple countifs. Multiplication (*) is used for AND, and addition (+) is used for OR in the logic.

    The formula:
    =SUMPRODUCT((Original!$H$2:$H$58=G$1)*(Original!$K $2:$K$58<=$C2)*(Original!$L$2:$L$58>=$C2))

    Tests that the country column equals at the top row (G1) , the Start date is <= the date in C2 and the End Dates are >= C2.

    I created a third chart.that shows the combined totals.
    The "ind Chart" does this when you select "(ALL)" from the pull-down...

    Question: Can this be interfaced with MS Access and scripted for automation?
    Yes, but unformtunately I don't use Access. If you want to do from Access, you may want to post your question on the Access board. You could export the MS data in the form like you have it in Excel and then have XL created the new table with code (for the dates and country names and formulas (sumproducts) in XL

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, Steve. I'll put the package together on the databases forum to see what the optimal solution should be. I don't mind keeping them separate, but since the workbook will have to be updated on a weekly basis based upon new data exports, some problems lend themselves to MS Access, but this Excel solution with some added scripting might be ideal for the reporting front end.

    Amy

Posting Permissions

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