Jump to content
  • 0

How To Put Aggregate Fields With Different Formulas In One Row?


Mylene
 Share

Question

7 answers to this question

Recommended Posts

  • 0
On 1/11/2016 at 10:27 AM, LWSChad said:

Use some CSS or JS to move things around... though pointing at an aggregate value is difficult as they don't have IDs.

something like

#caspioform td:nth-child(2), #caspioform td:nth-child(3), #caspioform td:nth-child(4) {
 position: absolute;
 top: 48px;
}

 Hi,

You may refer to this link on how to appy stylings to multiple sibling elements all at once:

 

Link to comment
Share on other sites

  • 0
On 1/11/2016 at 12:27 PM, LWSChad said:

Use some CSS or JS to move things around... though pointing at an aggregate value is difficult as they don't have IDs.

something like

#caspioform td:nth-child(2), #caspioform td:nth-child(3), #caspioform td:nth-child(4) {
 position: absolute;
 top: 48px;
}

I also need this solution. I have been searching for the past three weeks for a solution. Customer Support said it couldn't be done. The problem is, I don't know CSS or Java.

I am using a Chart and Report page. I have 9 fields that will have different but similar calculations based on the sum aggrigate calculations. Where/how would I install this solution?  What do each of the variables relate to? Where would I put the calculation? Do I still need to create an aggrigate entry for each of the nine items? If so, how do I hide the nine aggrigates?

I know this is a lot, but... I you can help me... I can't be the first one in this situation.

Link to comment
Share on other sites

  • 0

Coopper,

This is a GREAT Solution and I used it initially to get me half way there. The problem I'm having is as follows.

  1. I have 9 columns that I am totaling.
  2. The First Aggrigate (TOTAL) is a straight SUM across all nine columns - This works.
  3. Aggrigate 2 (REMAINING) is a calculation (SELECT CalorieGoal FROM MyProfile WHERE ProfileID =[@authfield:ProfileID#]) - SUM  - This works.
  4. Aggrigate 3 (moved to REMAINING) is a calculation (SELECT CarbGoal FROM MyProfile WHERE ProfileID =[@authfield:ProfileID#]) - SUM  - This works.
  5. Aggrigate 4 (moved to REMAINING) is a calculation (SELECT ProteinGoal FROM MyProfile WHERE ProfileID =[@authfield:ProfileID#]) - SUM  - This works.
  6. Aggrigate 5 (moved to REMAINING) is a calculation (SELECT SodiumGoal FROM MyProfile WHERE ProfileID =[@authfield:ProfileID#]) - SUM  - This works.

image.thumb.png.5cfa6a45b34dc9f1e742041608fc1217.png

The Problem

  1. I am out of aggrigates. CASPIO won't let me add any more aggrigates. I need five (5) more to complete the cornerstone dashboard of my application.
  2. I need to turn the negative numbers red and bold. I think I saw a solution for this so I am not too concerned.

Any ideas?

Lynda

Link to comment
Share on other sites

  • 0

Hello @Lynda,

It is possible to use 2 Aggregations fields to calculate the results if the field names in both tables are the same.

Let`s say I have the following the 'MyProfile' table:

qSVUTuO.png

 

And the table that is used to create a Report has the same fields names:

IWvlDe4.png

 

The 1st Aggregate is the SUM() function for 9 selected fields:

5gFSedi.png

The 2nd Aggregate has the following formula:

(SELECT SUM FROM MyProfile WHERE ProfileID =[@authfield:ProfileID]) - (SELECT SUM)

nSiuTsb.png

 

The trick is that when the field name to select is not specified it is taken from the Selected Fields. So, for each field the SELECT statement returns the corresponding value. 

Under the Calorie field it calculates: (SELECT SUM(Calorie) FROM MyProfile WHERE ProfileID =[@authfield:ProfileID]) etc.

To get the correct results the field names must be the same as in the 'MyProfile' table.

Output:

BwPI6uS.png

When the Table is in use in multiple objects (DataPages, Views, Relationships, etc.), it is challenging to change the field names. Though please see that it should be possible to cover the calculations with 2 Aggregation fields. 

Also, I added the following code to the Footer to change the color of the negative values:

<script>
if (typeof negativeValuesHandler == 'undefined') {

   const negativeValuesHandler = ()=> {

    document.querySelectorAll(".cbResultSetTotalsData").forEach(value => {  
                   if(value.textContent.startsWith('-')){
                        value.style.color = '#ff5a4b';  // change the color code if needed
                     }
          })
    document.removeEventListener('DataPageReady', negativeValuesHandler)
  }

 document.addEventListener('DataPageReady', negativeValuesHandler)
}
</script>

The values in Totals are bold initially, so I only added the font color by the script.

Hope this helps.

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