This article explains how the average and count unique functions operate within the `DR.GET` function in our Excel add-in, and how their results can differ when combined with the `DR.include` feature.

Function Overview

DR.GET Function

The `DR.GET` function is a special function in our Excel add-in designed to perform operations on data fields from a database. The function allows for filtering based on specific parameters and can include multiple parameters for a single field using the `DR.include` feature.

Syntax

=DR.GET(Value, "[Field1]", "Value1", "[Field2]", "Value2", ..., "[FieldN]", DR.Include("Param1", "Param2", ...))
Value: The function operation (e.g., average, count unique) to perform on a specific field (e.g., "Amount").
[FieldX]: Database fields to filter the query.
ValueX: Values within the database fields to filter by.
DR.Include: Allows for multiple parameters for a single field in the query.

Example Formula

=DR.GET(Value,"[Reporting Month]","12/31/24","[Scenario]","Actuals","[Animal Group]",DR.Include ("Cats","Dogs","Birds"))
In this example:
Value: Represents the function to perform, such as average.
[Reporting Month]: Field in the database to filter by "12/31/24".
[Scenario]: Field in the database to filter by "Actuals".
[Animal Group]: Field in the database to filter by "Cats", "Dogs", and "Birds" using `DR.Include`.

Average Function

Standard Average Calculation

The average function calculates the mean of the values in a data set. See this example:
Data: [2, 4, 6, 8, 10, 12, 14]
Calculation: (2 + 4 + 6 + 8 + 10 + 12 + 14) / 7 = 8

Average with DR.include

When the average function is combined with `DR.include`, the server calculates the average for each group defined by the parameters in `DR.include`, and then calculates the average of these group averages. See this example:
Formula: `=DR.GET("average","[Reporting Month]","12/31/24","[Scenario]","Actuals","[Animal Group]", DR.Include("Cats","Dogs","Birds"))
Data in tabular form:

DR.include parameter: Group by "Animal Group"
Calculation:
Average for Cats: (2 + 4) / 2 = 3
Average for Dogs: (6 + 8) / 2 = 7
Average for Birds: (10 + 12 + 14) / 3 = 12
Result: (3 + 7 + 12) / 3 = 7.33
Expected vs. Actual Results:
Without `DR.include`: Average of [2, 4, 6, 8, 10, 12, 14] = (2 + 4 + 6 + 8 + 10 + 12 + 14) / 7 = 8
With `DR.include`: Average of averages = 7.33
This example shows a case where the average of averages (7.33) is not equal to the general average (8).

Count Unique Function

Standard Count Unique Calculation

The count unique function returns the number of unique values in a data set. See example: 
Data: [2, 4, 6, 6]
Calculation: Unique values are [2, 4, 6]
Result: 3

Count Unique with DR.include

When the count unique function is combined with `DR.include`, the server counts the unique values for each group defined by the parameters in `DR.include`, and then sums these counts.
Example 1:
Formula: =DR.GET("count unique","[Reporting Month]","12/31/24","[Scenario]","Actuals","[Animal Group]", DR.Include("Cats","Dogs","Birds"))
Data in tabular form:

DR.include parameter: Group by "Animal Group"
Calculation:
Count Unique for Cats: 2 (unique values are [2, 4])
Count Unique for Dogs: 2 (unique values are [6, 8])
Count Unique for Birds: 2 (unique values are [10, 12])
Result: 2 (Cats) + 2 (Dogs) + 2 (Birds) = 6
Expected vs. Actual Results:
Without `DR.include`: Count unique of [2, 4, 4, 6, 8, 8, 10, 12, 12] = 6 (unique values are [2, 4, 6, 8, 10, 12])
With `DR.include`: Sum of count uniques = 6
Example 2:
Formula: =DR.GET("count unique","[Reporting Month]","12/31/24","[Scenario]","Actuals","[Animal Group]", DR.Include("Cats","Dogs","Birds"))
Data in tabular form:

DR.include parameter: Group by "Animal Group"
Calculation:
Count Unique for Cats: 3 (unique values are [2, 4, 6])
Count Unique for Dogs: 3 (unique values are [4, 8, 10])
Count Unique for Birds: 3 (unique values are [6, 10, 12])
Result: 3 (Cats) + 3 (Dogs) + 3 (Birds) = 9
Expected vs. Actual Results:
Without `DR.include`: Count unique of [2, 4, 6, 4, 8, 10, 6, 10, 12] = 6 (unique values are [2, 4, 6, 8, 10, 12])
With `DR.include`: Sum of count uniques = 9
This example shows a case where the sum of count uniques (9) is different from the general count unique (6).

Updated July 11, 2024 14:06