# Summarize Filtered data

I want to summarize data from a filtered table and can’t quite figure out how to get there

Below is an example of a summary table that uses TradeTime as the summary field. I use a 2 decimal number field for time so I can sort and filter easily.

I trade various hours of the day and want to be able to sort for different windows and see what the results are.

How do a create a “summary” of “summary data”?

For example, how do I find out my win rate from 9.45 to 10.35? If I filter for those times in the summary table I get only times in that range, but not a total for all trades in that time window.

I’m sure it’s not hard, I just couldn’t find how in the summary table documentaion

thanks

Hi Mike!

In this case, it’s best to create a table to run this calculation where you can specify a start TradeTime and end TradeTime then calculate the % of trades won.

I created an example for you here: Community #2633 - Grist

I have a table of Trades with TradeTime and a column called Won? to specify if the trade was won or not.

I have a summary table, grouped by TradeTime to show how many trades at that time, how many wins and then win %

Then I have a table called Win Calculations which is what you’ll want to create.

You have two columns to enter data in; TradeTime START and TradeTime END. This is where you’d enter those TradeTimes for your window to calculate the win rate.

You do not need to add the columns Trades in Trade Window and Wins in Trade Window. I added these to help explain the formula in the Win % column.

The formula in the Win % column is:

``````all_trades = Trades.all
window = []
wins = []
window.append(r)
wins.append(r)
# Calculate % wins
return len(wins)/len(window)
``````

We will break this down into chunks.

``````all_trades = Trades.all
window = []
wins = []
``````

First, we find all records in the Trades table and assign this list of records to the variable `all_trades`.

Next, we create an empty list and assign this empty list to the variable `window`. We create a second empty list and assign this list to the variable `wins`. We will add records to these empty lists based on criteria given in the next steps

``````for r in all_trades:
window.append(r)
``````

We need to find all trades that occur within the trade window. For each record in our list from the Trades table (assigned to the variable `all_trades` in the first step), we check to see if the TradeTime for that record is less than or equal to the time selected as our TradeTime END but greater than or equal to the time selected as our TradeTime START. If it falls in this given window, we will add it to the list assigned to the variable `window`.

After this for loop runs, we’ll have a list of all records where the TradeTime falls within the given Start and End window.

The Trades in Trade Window column shows the number of records in this list. For my example, 23 records falls within this window. We can confirm this by selecting those counts in the summary table and see that there are 23 records for the 4 TradeTimes that fall within that range.

``````for r in all_trades:
wins.append(r)
``````

Now, we need to find all trades that were won within the trade window. We do the same calculation to see if the record’s TradeTime falls within the given window but now we also check to see if the trade was won by adding `and r.Won_ == True`. If it falls in the given window and it was won, we will add it to the list assigned to the variable `won`.

The Wins in Trade Window column shows the number of records in this list. For my example, 2 trades were won that fall within this window. We can confirm this by selecting those counts in the summary table and see that there are 2 wins for the 4 TradeTimes that fall within that range.

``````return len(wins)/len(window)
``````

Last, we do our calculation to find win percents. len() counts the number of records in a list. So we find the number of records in our `wins` list (`2`) and our `window` list (`23`) then divide the two, giving us a Win % of `9%`.