Tabular Table - Hide all Data Points

[jhernandez]

Hello,

 

I have a tabular table with several fields.

 

There are two that I really need: Our earnings and Our paid losses per company (basically incoming vs outgoing monies), plus one calculated field that takes the "ratio" of loss (losses/earnings). I've grouped all data by month per year (ex, Jan 20016, Feb 2016, etc.) to make thing much simpler to read and aggregated the earnings and losses.

 

Since I have so much data, the individual earnings per company make the table rather large and separates them into several pages. What I need is to have the aggregate earning and losses plus the ratio per month per year only, I don't care about the individual companies that contributed to the aggregates.

 

Basically I want to do the same thing an excel pivot table does when it aggregates number WITHOUT the need to show the individual numbers that make up the total. Does it make sense?

 

Thank you,

[Mathilda]

Hi, 

You may try using the pivot table report: http://howto.caspio.com/datapages/reports/pivot-table/

 

[jhernandez]

Hi Mathilda,

 

Problem with pivot tables is that they can't calculate ratios for aggregate numbers, at least not in a good way. As a simple example, lets say we have the following data:

REGION A

John: $10 earned, $5 Loss - 50% loss ratio

Mary: $100 Earned, $1 Loss - 1% loss ratio

Mark: $50 Earned, $15 loss - 30% loss Ratio.

If I use a pivot table I can aggregate REGION A to $160 total earned and $21 total loss, but the ratio can only be SUM/AVERAGE/MAX/ETC and not calculated for the aggregate totals, which in this example would be $21/$160 = 13%. If I do a new aggregation as SUM, it will add each ratio to 81%, which is vastly wrong.

A tabular sheet can actually do the proper calculation as a calculated field, but it shows all data points. So basically I want to do what the pivot table does as a data sheet OR have a pivot table do a calculation based on aggregates rather than individual data points.