Jump to content
  • 0

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
 Share

Question

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))
 

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

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!

Link to comment
Share on other sites

  • 0

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' )

Link to comment
Share on other sites

  • 0
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]'

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...