I have an excel spreadsheet that is used to report on some logistics data per region.
Not every region has new data every day, so I was wondering if there's a way to report on the last captured day for a region, for example
Region 1 - last captured date / data is 1 Aug 2012
Region 2 - last captured date / data is 2 Aug 2012
Region 3 - last captured date / data is 2 Aug 2012
Region 4 - last captured date / data is 2 Aug 2012
Region 5 - last captured date / data is 5 Aug 2012
When I select the month, the last captured "date" per region should display (and ultimately I'd need to display the date as well) instead of having it filter if I select a day (list box) or show all for a month/year (also listbox). If I select a month/year, I'd like to see the last day each was captured and not just a summary of those captured on the last day (which is 5 Aug, and would only display Region 5, I'd like the others to display too)
I've simplified the dataset somewhat, but it should contain enough for this to work
To see what I mean, choose 2012/13 Financial Period, August, and then toggle between Day 1 / Day 2. I'll only be "choosing" the current month/financial period we're in, and the data should display the last date (with the expression that calculates stuff) it was captured in.
While toggling between Day 1 and Day 2, this will show you a bunch of regions that has data for Day 2 of August, excluding 1 that only had data for Day 1.
I'd like to show the one that had data for Day 1, with the others that had data for Day 2 (ie, the last date the bunch of records was last captured) for the Month of Aug (or whatever month is chosen, like I said, I'll always choose the latest financial period/month, but the selection should only do sum/expression work for the last date in that month that was captured)
Example:
Region, Actual Hrs, Date Last Captured
EC, 5, 1-Aug-2012
WC, 12, 3-Aug-2012
GP, 10, 1-Aug-2012
(nb, above totals are summed/expressioned for a bunch of records captured for that day, so GP will have 20 records captured for 1-Aug-2012, WC might have 12, EC might have 30)
