SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Age groups based on difference - Date slicer


Age groups based on difference - Date slicer

Author
Message
cidr
cidr
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 263
Hello,

I have a cube I'm working with that has a dimension called Employees.

In the dimension table contains a Birthdate.

What I need to do in a query is count the amount of Employees However, I also have to put them into age groups using the Birthdate. Below is an example of the dimension table:



Employee Birthdate
1 1951-04-04
2 1987-05-05
3 1986-11-04
4 1977-10-10




So, for simplicity, lets say three age groups;

Under 22;

22 to 34;

Over 35

I have no idea how I will achieve this as I need to use a Date slicer.

The way this would have to work is whatever dates in the Date slicer (MDX WHERE), would have to evaluate the date difference between the birthdate and the selected date. and then add them to the predefined groups so the example below could be shown.



<22 22-34 35>
EmployeeCount 15 40 70






Is there a way/function/MDX what I can create these groups and then group each of the employee? How would I store the birthdate for this to work; member property? attribute?

Any help would go a long way.

Thanks in advance
robert.gerald.taylor
robert.gerald.taylor
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1795 Visits: 1489
Couldn't you do this in a calculated member and use the DateDiff() function to get the number of years. Then use IIF() to put in groups?

Just thinking out loud here.

HTH,
Rob
cidr
cidr
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 263
robert.gerald.taylor (11/5/2013)
Couldn't you do this in a calculated member and use the DateDiff() function to get the number of years. Then use IIF() to put in groups?

Just thinking out loud here.

HTH,
Rob


Thanks for responding Rob. I can certainly look into it. The first thing I thought was DateDiff. Because I'm using the selected date, which is the slice, I'm not really sure how I'd use Datediff. If you have a simple example, I'd really appreciate it. then I'd need to put this logic into a function to reuse. My MDX skills aren't the strongest.


I'll go away and look at IIF() for groups.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search