Sum a column - column changes according to number ...

Tiger1 · 10-09-2019 12:11 PM

Can i add a virtual column to get the total number of items ([category]) in my table?

I have a table - “Utilization Report”

In this table i get data that changes every minute:

[Category] - Type of item i.e. - pump
[Sub category] - Description of that item
[Total inventory] - this changes during the day as customers rent items
[Total out] - changes accordingly
[Total on Hand] - changes accordingly

My question is - how do i get a total of the column - [category]? I want to know how many pumps there are total, out, on hand. I know i can “count” in group aggregate for a table view. But i want a view to look like this:
Total Inventory | Total Out | Total On Hand
Pumps 100 | 30 | 70
4" pump 50 | 40 | 10
6" pump 25 | 20 | 5

Does this make sense?

Tiger1

Sorry the table i put doesn’t line up correctly - hopefully you can understand what i am asking…

WillowMobileSys

Yes, certainly you can:

SUM(SELECT(Table Name[Total on Hand], [Category] = [_THISROW].[Category]))

Repeat for the other values you want to SUM.

Tiger1

Thank you

Tiger1

I only have the one table do i just plug into your expression (utilization report)?

Or do i even put table name - because its my only table for this app?

WillowMobileSys

You do need to use the physical table name. You had posted again saying the table doesn’t line up so I was just being general.

Also, you obviously would replace [Total on Hand] with the next value you want to sum up.

Tiger1

I added this 3xpression bit it seems to error my app out. I think there are too many rows in my table - over 16,000. Should I just try and do something in the table, instead of this app?

Wallace_Service

Hi Tiger, you could also use SUMIF/SUMIFS expressions in your spreadsheet (assuming that’s what you are using) to calculate the inventory amount and use Appsheet as the data collector and UI for reporting back inventory numbers.

WillowMobileSys

What is the error message? If you can, screen shot it and post it here. I don’t think 16,000 rows is an issue.

Tiger1

Got it - thank you

Tiger1

I will try it again and see if i get that error. I think it said “took too long to sync” so i assumed it was the amount of data.

Steve

“Took too long to sync” is commonly because of virtual columns, not data set size.

Tiger1

I used this expression:

SUM(SELECT(Utilization Report[Location_Total_on_Hand], [Category_code] = [_THISROW].[Category_code]))

Utilization Report = Table Name
Location_Total_on_hand = column i want the numbers from
Category_code = string column i need summed

Steve

That’s the only virtual column in your entire app?

How many rows in the table with this virtual column?

How many rows in the Utilization Report table?

Tiger1

that virtual row i think, should go through the entire table (utilization report) which is over 16,000 and sum up the Location_total_on_hand.

So - utilization report - 16,000
this virtual column - 16,000

Right?

Tiger1

Oh im sorry- yes its the only virtual column, so far. I will need a few others. Problem i am having here is this. My data table does NOT total anything. So i need totals.

Example:

Generator Totals
Pump totals
etc…

I also need branch (site) totals per item:
Beaumont has 100 pumps
Beaumont has 50 generators
etc…

Steve

So assuming the Utilization Report table is the table with this virtual column. The table has 16,000 rows. The app formula for the virtual column does a SELECT() against the Utilization Report table itself. That SELECT() examines every one of the 16,000 rows of the table. The app formula is evaluated for every row of the table. This means that for every row of the table, every row of the table is scanned. App formula evaluated for 16,000 rows, each scanning 16,000 rows. 16,000 x 16,000. 256,000,000 rows visited. 256 MILLION row visits for this ONE column.

This is why your sync is taking too long.

Every additional total you calculate this way multiplies the total by another 16,000.

Tiger1

Oh no. Ok, then how can i get totals? IS there another way?

Steve

Ideally, each (branch, item) pair would have its own row in another table and the totals would be computed there. That alone would be a huge improvement. Can you see a path to structuring your app that way?

Tiger1

I could create an excel table to get that info - however the original changes data every minute. So i would have to have it also update every minute? Also, can i have one excel spreadsheet send data to another?

Steve

If you want the totals up-to-the-minute, yes.

I don’t use Excel so I don’t know the answer to that.

Tiger1

ok . My table is in an excel spreadsheet that queries my database every minute. I could create another table with those categories and items - but when the original table updates - it needs to update the new table (i create). I am not sure that can happen.

My original table does NOT have totals - like in the excel spreadsheet (query) there are not totals. The whole point of my app was to give totals. But with all those rows, it seems impossible.

Steve

I’m afraid I don’t have any suggestions for your setup.

Tiger1

ok. Thanks for the help anyway. You always have great suggestions…

OptimiX_XcrY

I tried something similar for my app these days, and also failed.
I wish for some kind of custom group aggregate formula but no luck so far.

For the moment, my little workaround was to create a summary table, with only a dropdown to select a category, and virtual columns based on select formulas to calculate and show stuff related to selected category. So I can see totals, sums, etc, for one category at a time.

So I have :
-a huge table
-a slice of that table
-a summary table
-virtual columns in the summary table with select based formulas
-a view with quick edit for category dropdown, and show those virtual columns values right below

Tiger1

Thank you

Magu_Alejandro_

Im trying that, i wish i had a virtual col just for the UX

Sum a column - column changes according to number of items on hand