Dimension Functions
Spectre dimension functions work with a window of data, produce a side table for performing calculations, and extract values in various ways.
NOTE:
- These functions (with the exception of dimcount) can be used only in cPlans and Dives and only with the column tag.
- The dim* functions (with the exception of dimcount) plus the rank function work only on summary subexpressions.
- Dimension functions are labor intensive and can impact performance.
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
For example:
`dimaverage(calc("Order Count"), "Customer ID")` returns the average order count by customer id
`dimaverage(dimaverage(average(value("Survey Answer Value")), "SURVEY_SECTION_ID"), "SURVEY_ID")` calculates the average of the survey answer, then the average by survey section, and finally the average by survey
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
For example:
`dimcount("Customer ID")` returns the number of unique values in the Customer ID column found in the current working set
`filter( dimcount("OrderNbr"), value("DeliveryDate") = today() )` does a filtered dimcount using a date function. This expression can be used in a cPlan and the calc accessed by a Marker. The expression can also be used in ProDiver's Edit Column dialog—be sure to use backticks.
`dimcount("Patient", dimcount("Prescribers") > 5 and dimcount("Pharmacies") > 5)` counts how many patients have more than 5 prescribers and more than 5 pharmacies. The optional second argument is a filter to run over each dimension value that is being counted as if it were a filter on a window on the primary dimcount dimension.
NOTE: The simple form of this function (without a predicate) will work on a cPlan with a multilevel merge; however, the response time is slow.
TIP: Be careful with summaries in predicates on dimcounts. If the summary is meant to be calculated and used as a predicate for each value in the dimension being dimcounted, use the predicate variant of dimcount. If the summary is meant to be calculated for the dimensions in the window, use `filter()`.
For example:
calc "Accounts Sold" `dimcount("Customer", sum(value("Traditional Cases")) > 0)`
vs
calc "Accounts Sold" `filter(dimcount(value("Customer")), sum(value("Traditional Cases")) > 0)`
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
For example:
`dimgeomean(calc("Value"), "Mood")` returns the geometric mean of the summary totals by the mood column
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
For example:
`dimintercept(average(value("Y")), average(value("X")), "B")` returns a dimintercept of 0.0 for a1, and 3.0 for a2, with these Y, X and B values
The same expression returns -7.6229508 for a1 and 33.1047635 for a2 with these Y, X, and B values
See Math Functions for more on intercept().
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
For example:
`dimmax(calc("Order Count"), "Customer ID")` returns the max order count by customer id within the working set
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
For example:
`dimmmedian(calc("Age"), "Patient ID")` returns median age by patient id within the working set
`dimmedian(calc("Distance"), "Date")` returns median distance by date
calc "dimmedian" `dimmedian(sum(value("Frequency")), value("Color")) returns the median frequency by color
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
For example:
`dimmin(calc("Value"), "Mood")` returns the minimum for the mood column multitabbed with the primary dimension
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
Calculate the specified summary over the specified dimensions using the current working set, then return the value that occurs most often. The dimmode() function is the side table variant of mode().
For example:
Using this data:
Returns these results:
For item i1, when diving on Customer, the value of 100 is the most common value. Note that there is only a single line for every item/customer combination. If that is not the case, use a different summary or specify additional dimensions to meet this criteria.
For item i2, when diving on Customer, 1000 is the most common value. It is also the only value, but it occurs three times—it will therefore return 1000.
For items i3 and i4. when diving on Customer, all values (that is, the single value that exists) occur exactly once, so the result is null.
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
For example
`dimpercentile(calc("Age"), 25, "Patient ID")` returns the age below which 25% of the patient IDs fall
`dimpercentile(sum(value("Distance")), 50, "Date")` returns the median distance for date
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
For example:
`dimslope(average(value("Y")), average(value("X")), "B")` returns a dimslope of 1.0 for a1, and 2.0 for a2, with these Y, X and B values
The same expression returns 1.3729508 for a1 and 1.6232512 for a2 with these Y, X, and B values
See Math Functions for more on slope().
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
For example:
`dimstddev(calc("Value"), "Mood")` returns the sample standard deviation by mood for the specified summary
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
For example:
`dimstddevp(calc("Value"), "Mood")` returns the population standard deviation by mood for the specified summary
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
For example:
`dimsum(if (sum(value("Units")) > 100, 1, 0), "Customer" )` returns the number of Customers who ordered more than 100 units
`dimsum(info(value("Stock")),"Product Name")` returns the summary of the info() value of the stock amount for each product, that is where there is a one-to-one relationship between the stock and product
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
For example:
`rank(value("Industry code"), "Sales Region")` enumerates the rows on a Sales Region/Industry Code MultiTab (that is, numbers Industry code rows within each Sales Region)
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
See also: Alphabetized Functions for Spectre.