1. 

   twaters, Member

   Is there any way to perform calculations within aggregate functions?

   I would like to perform a countif() and then perform calculations based on the count if. I am using myDBR to perform subscription renewal calculations and reporting.

   TIA,

   Travis
2. 

   myDBR Team, Key Master

   Do you have a simplified example what you are looking for?

   --
   myDBR Team
3. 

   TJH, Member

   Hi,

   I guess Travis didn't respond but the type of calculation we are looking for is similar to Excel's PivotTable Calculated Field.

   For example, if I have a trend of Unique Visitors and Conversions. I would like to calculate Conversion Percent.

   So imagine I have Date in Column 1, Unique Visitors in Column 2, and Conversions in Column 3.

   Right now, I can do that within the query itself; however, if I'm doing aggregates in MyDBR, all I can do is an Average of Conversion Pct which doesn't make sense.

   So from an end-user perspective, I would like to do something like this:

   SELECT 'dbr.calc', '{3}/{2}*100', 'N/A', '%.2f%%'

   So, here I'm specifying a user-defined calculation, then an eval string of what the formula looks like. I'm using curly braces to define the ColumnReference substitutions since numbers can appear in the calculation. On an error (divide by 0, etc), 'N/A' should be displayed. And finally a sprintf format to format the number.

   I know sprintf is a bit dense, perhaps that can be simplified somehow. Maybe a specific keyword for percentages which only requires the user to specify the number of decimal places, like 'percent(,2)' or 'percent(1,2)'. The first formatting 0.25 as ".25%" and the second formatting is as "0.25%" - leading zeroes or no leading zeroes.

   Hopefully, this calculation would also work in cross-tabs so that the Grand Total column would do this calculation at the proper summary level.

   At some future point, I know we will want to have % of row total and % of column total. However, those are less important than being able to do simple custom calculations.

   Best,
   Trevor
4. 

   myDBR Team, Key Master

   Hi,
   For normal columns the calculations can be put in a query as you said. For now, we do not have functionality to do calculations based on dbr.sum, dbr.count etc.

   For your example, did not quite get where you wanted the dbr.calc result to appear? As an column (can do that already within the query) representing daily conversion rate or in the summary levels at some column (supposedly column 3) representing total conversion percent or both at same time?

   Being able to do calculations on summary levels could sometimes be handy. We'll put that into the TODO list to be evaluated.

   --
   myDBR Team
5. 

   myDBR Team, Key Master

   This feature will be included into next version. What you are going to be able to do:

   - Will perform calculations with summary levels
   - Will work with both normal and crosstable reports
   - Will support horizontal aggregates in crosstables
   - Will perform row-level calculations for horizontal aggregates automatically

   As an example, with command:
   

   select 'dbr.summary.calc', 5, 'C3/C4*100';

   You'll get:
   

   --
   myDBR Team
6. 

   TJH, Member

   I'm so sorry for not responding sooner but thank you for this feature. Our team has been making great use of it.

   T
7. 

   myDBR Team, Key Master

   The suggestion came from Travis and you, we just implemented it ;)

   --
   myDBR Team

Reply

You must log in to post.