Determine Date of Maximum Value by Year

[#1]

Hi Folks,

I have a dataset that includes daily snow depth data for a 20 year time period.  I need to determine the date by year when the peak, or maximum, snow depth value occurred.  Any suggestions on how to accomplish this?  Thanks.

[#2]

The general idea was outlined by PGstats above and also in the link provided by art297 above. Here's an example assuming you have table whose columns are Year, Day, and Depth. Day is a number from 1 to 365 or 366. It could instead be the actual date. In either case try out the following code. I tested it on a very small table with 3 years and 4 days per year and arbitrary depths (except I made sure there were multiple days with the max depth for each year. If you do not want to alter your main table you can replace the Table>Update command with the Table>Join command in which  case you would not choose the option to update the source table.

dt = Data Table( "Demo Max Depth" );

dtMax = dt << Summary( Group( :Year ), Max( :Depth ) );

dt << Update(

    With( dtMax ),

    Match Columns( :Year = :Year ),

    Add Columns from Update table( :Name( "Max(Depth)" ) )

);

dt << select where( :Depth == :Name( "Max(Depth)" ) );

dtDaysMax = dt << Subset( Selected Rows( 1 ) )

[#3 · Accepted solution]

The approach outlined by mpb is excellent for adding the maximum depth to the original table.  I've modified my original example to print out the maximum depth and date from the table.  I'm assuming that you have the fields Snow Date (date type) and Snow Depth (number).  I've added a column formula for a year column.

dt = New Table( "Snowfall",

    Add Rows( 20 ),

    New Column( "Snow Depth", Numeric, Continuous, Format( "Best", 12 ),

        Set Values(

            [34, 45, 53, 56, 99, 67, 12, 78, 53, 89, 77, 90, 48, 12, 73, 23, 12, 34,

            6, 45] ) ),

    New Column( "Snow Date", Numeric, Continuous, Format( "ddMonyyyy", 10 ),

        Input Format( "ddMonyyyy" ),

        Set Values(

            [1767225600, 1771113600, 1805155200, 1809129600, 1843084800, 1838160000,

            1889049600, 1893283200, 1896220800, 1900281600, 1950566400, 1955491200,

            1957651200, 1964217600, 1990396800, 1995926400, 2023488000, 2029449600,

            2051481600, 2056752000] ) ),

    New Column( "Snow Year", Numeric, Continuous, Format( "Best", 12 ),

        Formula( Year( :Snow Date ) ),

        Lock( 1 )

    )

);

// Get the highest snowfall for each year

summarize(year_list=by(:Snow Year), max_depth_list=max(:Snow Depth));

// Now get the dates of the highest snowfall

for (i = 1, i <= nrows(max_depth_list), i++,

    one_year = year_list[i];

    one_depth = max_depth_list[i];

// Could have the same maximum snow depth on different days

    one_year_max_rows = dt << get rows where(:Snow Year == num(one_year) &

                                             :Snow Depth == one_depth);

    for (k = 1, k <= nrows(one_year_max_rows), k++,

        m = one_year_max_rows[k];

        snow_date = :Snow Date[m];

        print("Maximum snowfall of " || char(one_depth) || " on " ||

                format(snow_date, "ddMonyyyy"));

    );

);

[#4]

Thanks PMroz for your helpful suggestion.  Your code did the trick and taught me a lot about how to solve these types of issues in the future.  Thanks everyone else for helping move the discussion along and offering interesting insights.