Filament•3mo ago

summarize calculated fields in footer

How can I add a custom calculation to the footer of a grouped table? These three columns show up great for each row and the footer group summarize works for the first two. Just cannot figure out the summarize for the third calculated column... Trying a custom summarizer:

TextColumn::make('leads_sum_connect_req')
    ->sum('leads', 'connect_req')
    ->summarize(Sum::make()->label('ConnctReq'))
    ->label('ConnctReq')
    ->description('ConnctReq'),

TextColumn::make('leads_sum_connected')
    ->sum('leads', 'connected')
    ->summarize(Sum::make()->label('Connected'))
    ->label('Connected')
    ->description('Connected'),

TextColumn::make('connected_percent')
    ->state(function($record){
        return ( $record->leads_generated->sum('connected')) / ($record->leads_generated->sum('connection_request')+ .01) * 100;
    })
    ->sortable()
    ->numeric(decimalPlaces: 1)
    ->summarize(
        Summarizer::make()
        ->label('Connected %')
        ->using(function($record){  // , but [$record] was unresolvable.
            return 99.9 ; // to test
            return ( $record->leads_generated->sum('connected')) 
                  / ($record->leads_generated->sum('connection_request')+ .01) * 100;
        })
)

TextColumn::make('leads_sum_connect_req')
    ->sum('leads', 'connect_req')
    ->summarize(Sum::make()->label('ConnctReq'))
    ->label('ConnctReq')
    ->description('ConnctReq'),

TextColumn::make('leads_sum_connected')
    ->sum('leads', 'connected')
    ->summarize(Sum::make()->label('Connected'))
    ->label('Connected')
    ->description('Connected'),

TextColumn::make('connected_percent')
    ->state(function($record){
        return ( $record->leads_generated->sum('connected')) / ($record->leads_generated->sum('connection_request')+ .01) * 100;
    })
    ->sortable()
    ->numeric(decimalPlaces: 1)
    ->summarize(
        Summarizer::make()
        ->label('Connected %')
        ->using(function($record){  // , but [$record] was unresolvable.
            return 99.9 ; // to test
            return ( $record->leads_generated->sum('connected')) 
                  / ($record->leads_generated->sum('connection_request')+ .01) * 100;
        })
)

With this I get an error:

An attempt was made to evaluate a closure for [Filament\Tables\Columns\Summarizers\Summarizer], but [$record] was unresolvable.

An attempt was made to evaluate a closure for [Filament\Tables\Columns\Summarizers\Summarizer], but [$record] was unresolvable.

9 Replies

nanopanda•3mo ago

@ddoddsr ->using() only allows you to inject the $query and modify it. The Summarizer works with aggregate query functions, so I think your best bet would be to change your calculated Table column into a virtual/stored DB column. https://laraveldaily.com/post/virtual-db-columns-laravel-migrations-mysql

Laravel Daily

Virtual DB Columns in Laravel Migrations and MySQL

ddoddsrOP•3mo ago

I am trying to make a custom summary a la https://filamentphp.com/docs/3.x/tables/summaries#custom-summaries that would calculate the percentage of the sums of the two oher columns

Summaries - Table Builder - Filament

nanopanda•3mo ago

You can't accomplish it that way. A custom Summarizer can modify the $query with an aggregate calculation function ( passed to DB ), but it can't perform in-memory calculations for each record. The calculated columns only work because they can run for only the visible records after the query is fetched.

ddoddsrOP•3mo ago

OK thanks. I think if I use the custom fields to make the calculations I'd have to average the calculated column and that would not be correct math. I need the sum of the two fields to get the percentage. Any ideas?

nanopanda•3mo ago

@ddoddsr gotcha, I'm not a math expert, but if you are using a Average Percentage formula like: [(Percentage 1 + Percentage 2 ... + Percentage N) / (Sample size 1 + Sample size 2 ... + Sample size N )] x 100 Then I suppose you would need two virtual columns for storing the percentages and sample sizes. Then I think you could make your Summarizer ->using() function perform the final calculation like:

return ( $query->sum('percentages') / $query->sum('sample_sizes') ) * 100

return ( $query->sum('percentages') / $query->sum('sample_sizes') ) * 100

ddoddsrOP•3mo ago

I'm not following. Does this give grater weight to the records with more numbers? I'll hve to ply with this and let you know. I played with this on s google sheet:

ddoddsrOP•3mo ago

nanopanda•3mo ago

@ddoddsr In your case I think you are already storing the percentage of sample size ( Connect ) and Sample size ( Request ). So can change the Summarizer ->using() function to something like:

->using(function($query) {
            return ( $query->sum('connected') / $query->sum('connection_request') ) * 100;
        })

->using(function($query) {
            return ( $query->sum('connected') / $query->sum('connection_request') ) * 100;
        })

See here also: https://www.omnicalculator.com/math/average-percentage

Omni Calculator

Average Percentage Calculator

The average percentage calculator can take up to ten percentage values with possibly different sample sizes and return the average percentage of the entire dataset.

ddoddsrOP•3mo ago

I am storing only the count of requested and count of connected for each record, calculating the percentage on the fly. I think the difference is that I need to use $query in my calculation. I'll post results... Thanks for the guide, I did this one with selectRaw statements but will try the weighted percentage in the next page I build.

Gaming

Programming

summarize calculated fields in footer