Column Total in a Statistic Component

[shu]

Hey there.

I have a table in Retool that's pulling from BigQuery. My table contains names and the units associated with them. I'd like to include a statistic component to show the total units pulled from the query.

Example record:

item | units
a 100
b| 50
c | 25

Result would have a component with 175.

Per this post, I tried to use:
{{_.sum(formatDataAsObject(query1.data.units)['units'])}}, but this doesn't work for me. It simply returns 0.

Not sure what I'm doing wrong in my query. If you have a better solution, I'd greatly appreciate some assistance.

[ScottR]

I think you would have to run a query JSON with SQL
So, if your table contains units as a column, you can run
select sum(units) as total_units from {{yourtable.data}}
In my example, I totaled sales...

[shu]

It looks like I'm running into an issue earlier down the line.

The above shows 0, but I have many records with units in my table. When I backtrack, I see that a lot of the records show undefined for the column. Do you have an idea why that is?

[ScottR]

I thought you wanted to sum them all up? if so, you should replicate the query I posted above...
The only thought here is that you may not be reading the initial data from the db correctly.

What does the table component look like?

Can you post the JSON from that table?

[shu]

I do want to sum them up, that's what the first screenshot shows me doing. I posted the second screenshot to show that the records are returning as undefined, which is my assumption as to why they aren't summing properly.

Here is the table:

The statistic component looks like this:

[ScottR]

The Primary value should be keying off the Query JSON with SQL (assuming that for you is query32, but query 32 is NOT the same as the one I posted...
query32 should be like
select sum(budget_units) as budget_units from {{data_table.data}}
then your primary value sot statistic1 should be like
{{query32.data['0'].budget_units}}

[shu]

Even if I use the query similar to what you share, the value I get returned is 0. But the table itself has values in the Budget Units column.

This here is my query32 SQL that I'm using in the statistical component.

At first, I thought it was a datatype issue. However, if I use select SUM(CAST(budget_units AS NUMERIC)) as budget_units from {{data_table.data}}, I still don't see any value.

Thanks for helping me out with this. I'm at a loss.

[ScottR]

Just to be sure that your data_table.data is formatted properly, try
select sum(budget_units) as budget_units from {{formatDataAsArray(data_table.data)}}

[shu]

All right, that's some progress. I'm getting values now, but an astronomically large number:
budget_units: "10001001001001500360048010001504003002005000072025018003060111603609060609002402400150000060005024030120200600400500200160240600050604010300600700"

If I query the actual database in BigQuery, the total units in the table is only 1835270

Attached a screenshot of the table to show what's up:

[ScottR]

What is the query you run directly?
Your table has 8000+ rows also, and I don’t think it will matter but change “as budget_units” to “as total_units” no quotes and then put total_units in the primary value. Just to be sure the query json isn’t double counting because the reference has the same name as the original column name

[shu]

The query:

SELECT DISTINCT
  territory,
  currency,
  budget_units
from fnd.sales_forecast
limit 5
offset {{ data_table.paginationOffset }}

I changed the sum query

select 
sum(budget_units) as total_units 
from {{formatDataAsArray(data_table.data)}}

Primary value is {{query32.data['0'].total_units}} and I'm getting 10001001001001500 when I should be getting 2,800.

EDIT: FYI removing the pagination doesn't make a difference, I tried running the query with and without.

[ScottR]

Please send me the data/payload from the direct query and I will reconstruct on my end and find the issue. I am not able to do so at the moment but I will get to it tomorrow in the morning if you can wait

[shu]

Here is the sample data I've been using. Appreciate all the help!
sample_dataset.csv (87 Bytes)

[ScottR]

The sample data you sent me works as expected:

budget_units add up to 2800 as expected when running:
select sum(budget_units) as total_units from {{ table8.data}}

[shu]

Hey Scott. I'm not sure why it was working for you but not for me. I talked with some other devs and they gave me a solution using a JS transformer looks like this:

let budget_unit_total = {{data_table.data.budget_units}};
return budget_unit_total.reduce((p,s) => p+s)

This works when every record has a value, but I have some columns with null values that stop this from working.

I tried changing the variable to let budget_unit_total = {{data_table.data.budget_units || 0 }};, but it still doesn't fix anything. Any ideas?

[lauren.gus]

Hey @shu Would you mind sharing a screenshot of what data_table.data.budget_units looks like? What error are you seeing when you run this query?

[shu]

Hi @lauren.gus ,

I actually was able to get it to work with some help from the Retool chat. The issue was that the datatype of one of the columns was a string, so it was concatenating the numbers together instead of adding them.

I changed the code to this:

let budget_unit_total = {{data_table.data.budget_units || [0] }};
return budget_unit_total.reduce((p,s) => +p + +s)

to convert the values to numeric. Now we're all good to go!

Thanks everyone for trying to help me out through this!

[lauren.gus]

Ah so glad to hear it