Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2007
    Posts
    7

    Red face Unanswered: Time Series in Pivot Chart... creating the gaps

    I'm exasperated with this issue and dearly need some help. I work with a team of epidemiologists who create these epicurve charts using Access pivot charts. However, try as I might, I cannot get these pivotcharts to display the category axis in a time series. The sql statements I use only return an aggregate of disease cases for the weeks that they occur so if there is a week without a disease incident then it won't get displayed. For common diseases like influenza, all weeks of the year get displayed - great! - but this isn't the case for the less common diseases and creates a false impression. I'd happily use a vanilla stacked bar chart except the epi's love doing their analysis with their pivotcharts.
    Does anyone know how I can modify my sql to include zero cases for the missing weeks or somehow tweak the pivotchart to display a complete time series? Thank you.

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    If you create a table that only has dates in it, one date for each week, then you can reference this "time" table in your SQL to be sure that each week is included in your time line, even if there are no sick people that week.

  3. #3
    Join Date
    Jul 2007
    Posts
    7

    Unhappy

    Thanks GolferGuy. I did try creating a table with WeekID and ZeroCount fields. WeekID went from 1 to 53, ZeroCount was all zeroes. Joined it to my original query and created a calculated field that added the zero to the cases. Doing it this way seems to require the case year as well (the epi's like to see 5 years of disease peaks) so I created another field with the single year 2000 in tbl_WeekNO. Anyhow, I still couldn't get it to work, still missing weeks and not getting the zero value. The sql is below:

    SELECT tbl_WeekNO.WeekID, [Cases]+[ZeroCount] AS Combined
    FROM tbl_WeekNO LEFT JOIN qry_Weekly ON tbl_WeekNO.WeekID = qry_Weekly.Week
    WHERE (((qry_Weekly.Year)=2000))
    GROUP BY tbl_WeekNO.WeekID, [Cases]+[ZeroCount]
    ORDER BY tbl_WeekNO.WeekID;

    If there is another way that doesn't require me building 20 historic years worth of weeks (plus the future years!), something a bit more elegant then I'd go for that but if that is the only way to do it, I'll do it. If I can get it to work! Why did Microsoft build a pivotchart that won't do time series?

  4. #4
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    WHERE (((qry_Weekly.Year)=2000))
    This needs to be from tbl_WeekNO because tbl_WeekNO is what is giving you the filler for the gaps. But it you are only accepting records where there is a year of 2000 in qry_Weekly, then you will be excluding all the records from tbl_WeekNO that you are really wanting in.

    But the better way to do this would be to have Qry_Weekly have the year criteria in it, then you can just leave the year code out of tbl_WeekNO.

  5. #5
    Join Date
    Jul 2007
    Posts
    7

    Talking

    Thanks GolferGuy - spot on! Works a treat, first time. Wish I'd joined this forum ages ago instead of mucking around wishing something would fall into place. If you're hitting the greens this w/e GolferGuy, may all your shots fly true.

    "What other people may find in poetry or art museums, I find in the flight of a good drive". ~Arnold Palmer

Posting Permissions

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