How do you write an expression to sum the val...

expressions
(Tammi Canelli) #1

How do you write an expression to sum the values in a single column?

I created a VC to get the values for each “Yes” in a column, but now I want the total of all the “yes’s” for that column.

Sum([col]) triggers an error.

(Levent KULAÇOĞLU) #2

@Tammi_Canelli SUM(SELECT(TableName[Col],[YesColumnName]=“Yes”))

(Aleksi Alkio) #3

COUNT would be probably better :wink:

(Aleksi Alkio) #4

You need to create an additional table and calculate values there.

(Tammi Canelli) #5

I’m trying that too.

I have added a totals table.

Made sure the column names match the table I needs totals from but not making any headway finding expressions that will work.

The expressions in Appsheet make me want to throw my computer out the window

(Praveen Seshadri (AppSheet)) #6

So let’s say you have a totals table and it has just one row with one actual column “MyTotal”. Now add a virtual column to it and give it the formula you wrote earlier: COUNT(SELECT(Medical Needs Program Registry COMPLETED NORTH [RegID],[O2 under 5 lpm]=“Y”))

it should compute 276 or whatever is the right count. That’s it.

If what you really want is for this total to be computed in a spreadsheet cell, it might be simpler to just use a spreadsheet formula in that cell rather than having the app compute it.

(Allan Casal) #7

If you need to see the totals in the sheet, how about using a formula in the sheet itself?

If you also want to see totals in the app however, you’d need to create a table as Aleksi suggested. Your Totals col should be a VC so it would update automatically every time the app syncs. Your formula above should work fine here.

(Tammi Canelli) #8

Thank you.

I got all totals to work.

I couldn’t do them all in the sheet because I hit the 25,000 cell limit for formulas in Smartsheet.

I did end up having to create virtual columns for a few checkbox fields in order to get the values and then did a sum of all the values in my “totals” table.

Will this create an issue since virtual columns re-calculate more often?

It won’t cause my numbers to double then triple, etc will it?

(Praveen Seshadri (AppSheet)) #9

In general, virtual column computations proceed in a sensible dependency order. You should be able to check to see if they appear correct. They will get recomputed from scratch each time you sync.

(Tammi Canelli) #10

This is what I used in the VC: =IF([O2 over 5 lpm],1,0)+IF([O2 under 5 lpm],1,0)

(Tammi Canelli) #11

COUNT(SELECT(Medical Needs Program Registry COMPLETED NORTH [RegID],[O2 under 5 lpm]=“Y”))

Totals them but shows by row.

How can I get just a summary row?

(Stephen Mattison) #12

@Tammi_Canelli

=SUM ( SELECT (OrderDetails[LineTotal], [OrderID] = [_THISROW].[OrderID]))

This is the expression I use in a VC in Orders Table to give me an [OrderTotal] of

the [LineTotal] from each OrderDetail for that Order.

Something like this should work for you…

(Stephen Mattison) #13

@Tammi_Canelli

Well, now that I look at yours better, I think you’re wanting more than what I shared earlier, sorry.

(Tammi Canelli) #14

I’ve tried using _thisrow in a few but the test comes up blank.

I just need that 276 number to show in the totals col I created…grrr… so frustrating

(Aleksi Alkio) #15

I’m afraid you are looking for something that is not possible. You are trying to create a summary row where you can have all calculated or counted values. Am I correct?

(Tammi Canelli) #16

Yes.

So there is no way to get column totals?

(Tammi Canelli) #17

Sorry i am not following.

what is the first [col]? the key column?

Would I do this in the same table in a VC or can i do it a separate table?

I really want the totals to go to a sheet and i know VC won’t.