Divide the sum of the total value of Column A(Field) and the result will be divided by the total sum of column B(Field).

[Ewens]

I need to replicate our spreadsheet data formulas into Caspio.

I need a simple spreadsheet style calculation where I will divide the sum of the total value of Column A and the result will be divided by the total sum of column B.
Its look like this on google sheet =sum(A2:A)/(sum(A2:A))
 

[GoodBoy]

Hi @Ewens

You can try creating SELECT statements inserted in a calculated field. For example:

(SELECT SUM(ColumnA) FROM TableName WHERE Condition)/(SELECT SUM(ColumnB) FROM TableName WHERE Condition)

For more information, you can browse this article or watch this video tutorial as well: 

https://howto.caspio.com/faq/reports-datapages/calculated-fields-and-datediff-function/

https://www.youtube.com/watch?v=15zAruBT3LA

 

Hope this helps!

[Ewens]

Thanks @GoodBoy it worked, but we also have to make it dynamic base on search filter. I am using a date range with 2 criteria for the filter. 

Base on this formula the result is always the base on grand total of entire table
(SELECT SUM([@field:SALES]) FROM stm_tbl_contacts  WHERE Call_Outcome = 'Sale/Offered' )

[TellMeWhy]

5 hours ago, Ewens said:

Thanks @GoodBoy it worked, but we also have to make it dynamic base on search filter. I am using a date range with 2 criteria for the filter. 

Base on this formula the result is always the base on grand total of entire table
(SELECT SUM([@field:SALES]) FROM stm_tbl_contacts  WHERE Call_Outcome = 'Sale/Offered' )

Just include the dates in the criteria. On Search Filters, enable the On Exit, and then you can then use these parameters on the WHERE (i.e. WHERE Call_Outcome = 'Sale/Offered' AND DateColumn >= '[@DateParameter1]' AND DateColumn <= '[@DateParameter2]'

[Ewens]

Thanks @TellMeWhy this is perfect.