Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using a Metric within a Metric

Using a Metric within a Metric

Using a Metric within a Metric

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.

RE: Using a Metric within a Metric

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close