How to aggregate calculated ratios?

Stephen Johnson shared this question 1 year ago
Answered

I have some transit data that I want to analyze. At the view level I have data for each vehicle/day with the passengers transported that day and the amount of service time. I create a calculated field to compute passengers per hour (passengers/time), which works correctly, but I don't know how to aggregate that metric at the report level.

If I aggregate data for a single vehicle across multiple days I want to use SUM(passengers)/SUM(Time) to see the performance of that vehicle for that date range, but the only thing I can figure out how to do is sum the calculated field which works out to SUM(passengers/time) which is not correct.

How do you configure this kind of aggregation in Yellowfin?

Comments (3)

photo
1

Hi Stephen,

These types of questions are typically difficult to answer as it is often very specific to how your data is structured, but it is worth noting that you can include aggregations in calculated fields to make the

Sum(A)/sum(B)

That you describe. Have you tried this equation as your calculated field?

Nathan

photo
1

That seems to have worked. Thank you Nathan!

It was counter intuitive at first that I would include the sum in the calculation at the view level. I guess when displaying data in a table format it's just summing one value.

photo
1

Hi Stephen,

Awesome! Yea, how aggregations build into the final SQL statement can be a bit tricky. If you want to see what exactly is happening, I would recommend using the "View SQL" button (in the lower right corner of the "data" tab) frequently :)

Nathan