Using a Metric within a Metric

[kinetic123]

I have the following table layout

Store_employee_details (that indicates the assignment of employees to stores for days in a month)

day_key store_key employee_key
1 S1 E1
1 S1 E2
1 S2 E3
2 S2 E1
2 S1 E2
2 S1 E3
2 S1 E4
2 S1 E5

I have a metric - Count(employee_key) that provides the number of employees assigned to each store for each day

The output would be

day_key store_key count(employee_key)
1 S1 2
1 S2 1
2 S1 4
2 S2 1


Let us assume that the day_key are in a hierarchy and fall under a month_key, lets say M1 for example

I would now like to have a metric - that displays the median of the count(employee_key) for the month, for a store.

Note: I need a metric and not a subtotal.

the output should look like
month_key store_key count(employee_key) Median
M1 S1 5 2
M1 S2 2 1

I tried using metric function.. but it looks like i am messing with the levels of dimensionality.

Pl. provide your inputs on how to achieve this.

 

[nlim]

this is standard nested aggregation, straight forward in MicroStrategy hard in other tools: if you have vmall demo set up, you can see it in the vmall>>public objects>>reports>>c. reports by feature>>analytics>>metrics>>nested aggregation example.

basically you want to create metric dailyemployeecount M1, and in the dimensionality section have it set to day and report level.

You will need another metric employeecount M2.
Then create a third metric M3 defined as median(M1).

On your report place put Month, Store, M2 and M3. this will work for you.

In essence your M3 defintion is median(employee count first calculated at the daily level) The SQL generated will first calculate employee at the daily level. Then use these data to calculate the median metric group by month.