Dimension Functions

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

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)`

For example:

`dimgeomean(calc("Value"), "Mood")` returns the geometric mean of the summary totals by the mood column

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().

For example:

`dimmax(calc("Order Count"), "Customer ID")` returns the max order count by customer id within the working set

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

For example:

`dimmin(calc("Value"), "Mood")` returns the minimum for the mood column multitabbed with the primary dimension

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.

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

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().

For example:

`dimstddev(calc("Value"), "Mood")` returns the sample standard deviation by mood for the specified summary

For example:

`dimstddevp(calc("Value"), "Mood")` returns the population standard deviation by mood for the specified summary

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

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)