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

[Mylene]

Hi everyone!

 

I have 3 aggregate fields with different formulas.

It works fine, but every field located on different row.

But I'd like to put all these fields in one row. 

Is it possible?

 

Thanks!

Mylene

[LWSChad]

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;
}

[futurist]

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:

 

[Lynda]

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.

[CoopperBackpack]

Hello @Lynda,

I shared formula in this post: 

 

Perhaps, it can work for you as well. Could you share the formulas you use in Totals to suggest the approach? 

[Lynda]

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.

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

[CoopperBackpack]

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:

 

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

 

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

The 2nd Aggregate has the following formula:

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

 

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:

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.

[Lynda]

Coopper... YOU ARE AWESOME!!!! You should be paid a million bucks for this solution. You Solved two major items and roadblocks that I had.

THANK YOU.

Lynda

[Kronos]

Hi,

I just wanted to add to this thread. There is also a forum thread that talks about the same topic:

I hope this helps!