subtracting dates 1

[benbotektips]

I have to find a number of days between a registration & activity, 2 date fields. These date fields are attributes. I'm trying to get the average number of days, for many rows within 1 group. So, the heirarchy is

company avg. days between reg. & activity.

The 'Apply Simple' formula I keep trying will only work on metrcis, and the metrics expect counts & sums. How can I get the average number of days for a group? Does there have to be a separate attribute (not metric) to do any math on the contents of date fields? Or can i create a metric to get a number as a result of date field subtraction?

 

[entaroadun]

The best solution is to define "Number of Days" as a fact, then build a metric named "Average Number of Days" using the Average function on this fact. Use the ApplySimple function in the fact itself, and be sure to change the data type of the fact expression to "Number".

 

[benbotektips]

I don't have permission to create a fact, I may get to create an attribute. Can I accomplish the same thing by creating an attrbute with the ApplySimple fiormula?

 

[entaroadun]

Yes, but it's not a good solution. Attributes have dimensionality; they are fixed in place at a given level in a dimension. If you want to create an object that only works at the company level, this is adequate.

A better solution is to create a metric using ApplyAgg. Metrics have no dimension; instead, they require an aggregation function so MSTR knows how to roll up data to the level required in the report. ApplySimple can't work in a metric, because it has no aggregation function. ApplyAgg does.

Create a metric:

ApplyAgg("(Avg(#1-#0))", [Registration Date Attribute]@ID, [Activity Date Attribute]@ID)

The "@ID" part means that you are using the ID attribute form. Metrics can only work on specific columns; attributes may contain multiple columns as forms. You need to specify which form to use whenever you do a calculation on an attribute in a metric.

The reason I prefer to create a fact is that the MSTR SQL engine is sometimes weird with metrics on attribute forms. You always know what you're going to get with facts. I use facts in all of my metrics, even Count metrics on attributes.

 

[benbotektips]

I got a fact created, made a metric to get avg(the fact), and it worx great. Well done!