Excel 2007 : DMax with 2 criteria, one being time.

[jbrinkley0826]

I pull a report from a database that contains the following information.

key time value
1111 8/1/2010 08:30 500
2222 8/1/2010 08:30 800
3333 8/1/2010 08:30 700
4444 8/1/2010 08:30 1000
1111 8/1/2010 09:00 550
2222 8/1/2010 09:00 820
3333 8/1/2010 09:00 755
4444 8/1/2010 09:00 1010
1111 8/1/2010 09:30 560
2222 8/1/2010 09:30 840
3333 8/1/2010 09:30 770
4444 8/1/2010 09:30 1010


I am at wits end trying to use the dmax and just can't seem to get it. I need to get the max value for each key each day. the report has a months worth of data on it. so I need two criteria in the formula, key and time. Any help would be appreciated, I did a search but couldn't find anything on it. thanks in advance.

I'm thinking it will have to have two times, a greater than, and a less than.

[shg]

Welcome to the forum.

Post a workbook?

[jbrinkley0826]

inserted workbook

[shg]

How about a pivot table? I separated the date and time into two columns, but that's probably not necessary (I am not a pivot table wiz).

Please Login or Register  to view this content.

[jbrinkley0826]

yeah, I did that, but I need to put a whole months worth of data onto one sheet. I don't know that a pivot table has the capability to run multiple criteria such as looking for the peak kw for multiple days for each key.

oh wait, maybe you are on to something. I'll have to play with the pivot table more this weekend when I get back to work. thanks

Jeff

[jbrinkley0826]

I think you are underestimating yourself. that is exactly what I am needing, I just don't seem to be able to figure out how you did that. could you share how you did all that? thanks

[shg]

I inserted a column following your date/time column, and used a formula to extract just the date ( =int(datetime) ). Then I copied the column, replaced with values, and deleted the original date/time column.

Then I created a pivot table, put the codes and dates in the row area, and the values in the body of the table, then changed the body to Max.

I'll ask someone else to stop by and explain it intelligently.

[DonkeyOte]

In theory you don't need to separate date from time - you can instead Group the time field by Days.

Using 2007 you can create the Pivot using Table_Query_from_OSI_SQL_DB_Connection as source.

Set osi_key and time as row labels and value as data field (set to MAX).
Right click on any time value and select Group -> Days (de-select other options) -> OK

When the Pivot is active you will find the PT Bars (Options / Design) on the Ribbon appear - you can use these to adjust the Pivot as you see fit.

[shg]

I'll ask someone else to stop by and explain it intelligently.

See the value in that?

Thanks, DO.

[jbrinkley0826]

ok, got it figured out, thanks for the help!!!!!

[jbrinkley0826]

wow, you guys are awesome! Now all I need to do is figure out how to get the time of the max and I'll be good. If someone knows right off hand how to do that, please let me know. I'll start working on it now. thanks again for all the help!!!

[DonkeyOte]

what is to be the output where multiple instances of the MAX value exist for a given key/day ?

You may find it will be easier to add some additional (basic) calculations to your existing Table, eg:

Please Login or Register  to view this content.

You can then revise the Pivot layout such that

Report Filter: IsMax (set to TRUE)

Row Label: osi_key, time (do not group)

Data Area: value

You should then have a layout that lists the DateTime(s) where the daily MAX occurred for a given osi_key (on a daily basis)

Note: the above calculations are based on the premise that the source data is sorted by key and then time - as implied by sample

[jbrinkley0826]

hmmmmm, having a few problems with that. When I insert the "key" id D1, it becomes "key2" because its in a database and you can't have two column names the same. well, I can't get it to work anyway.

also, is there a way to filter the data in the pivot chart so that it only shows the previous thirty days of data? I can't do it in the query because there are two tables put together. man, this is turning out to be a nightmare for me.

appreciate all the help.

[DonkeyOte]

No need to quote prior posts in your reply - only those parts you deem necessary to maintain a logical flow to the thread.

When I insert the "key" id D1, it becomes "key2" because its in a database and you can't have two column names the same. well, I can't get it to work anyway.

The text used for D1 is of no real consequence - it's simply a column heading and as such can be whatever you wish.

is there a way to filter the data in the pivot chart so that it only shows the previous thirty days of data?

You can but to make dynamic it might make sense to use yet another field at source.

See attached
(note I cut down amount of data in sample for sake of file size though still large-ish - 2.5k rows of data ... also Pivot created in XL2007)