Friday, August 22, 2014

Calculating an Age

One of the questions you often get is how to calculate a customers age on a certain date. Luckily Chris Webb published a great example: http://cwebbbi.wordpress.com/2014/06/09/calculating-ages-in-mdx/.

I made a small extension to this by making it level aware, this means you can also use it at year, semester, quarter and month level.

To make it level aware I use the .Level.Ordinal extension on the Currentmember. This returns the Level of the member within the Hierarchy (0 = All, 1 = Year, 2 = Semester, 3 = Quarter, 4 = Month, 5 = date).

 

WITH
-- Decide which date we are going to use
-- In this example we use the First date of a period
-- If you want to use the last date of a period
-- Change FirstChild To LastChild
-- Based on a orignal design from Chris Webb
-- http://cwebbbi.wordpress.com/2014/06/09/calculating-ages-in-mdx/
--
  MEMBER measures.dateforcalculation AS
CASE
  WHEN [Date].[Calendar].currentmember.level.ordinal = 1 THEN
[Date].[Calendar].currentmember.firstchild.firstchild.firstchild.member_value
  WHEN [Date].[Calendar].currentmember.level.ordinal = 2 THEN
[Date].[Calendar].currentmember.firstchild.firstchild.member_value
  WHEN [Date].[Calendar].currentmember.level.ordinal = 3 THEN
[Date].[Calendar].currentmember.firstchild.member_value
  WHEN [Date].[Calendar].currentmember.level.ordinal = 4 THEN
[Date].[Calendar].currentmember.member_value
  ELSE [Date].[Calendar].currentmember.member_value
END
  --calculate the difference between the years of the 
  --current date and the customer's birth date
  MEMBER measures.yeardiff AS
    datediff(
      "yyyy",
      [Customer].[Customer].currentmember.PROPERTIES("Birth Date", typed),
      measures.dateforcalculation
    )
  --calculate a value which is the month number
  --multiplied by 100 plus the day number of the month
  --for the current date
  MEMBER measures.datemonthday AS
    (month( measures.dateforcalculation) * 100) + day(
    measures.dateforcalculation)
  --calculate a value which is the month number
  --multiplied by 100 plus the day number of the month
  --for the customer birth date
  MEMBER measures.birthmonthday AS
    (month([Customer].[Customer].currentmember.PROPERTIES("Birth Date", typed))
    *
    100) + day([Customer].[Customer].currentmember.PROPERTIES("Birth Date",
    typed)
    )
  --calculate customer age as the difference in years
  --minus 1 if the customer's birthday this year is
  --after the current date
  --For all levels except (All)
  MEMBER measures.customerage AS
    CASE
      WHEN [Date].[Calendar].currentmember.level.ordinal = 0 THEN null
      ELSE measures.yeardiff -
    IIF(measures.datemonthday>=measures.birthmonthday, 0, 1)
    END
SELECT
  {
    [Date].[Calendar].allmembers
  } ON 0,
  --return all customers in Coff's Harbour on rows
  {
    (
      DESCENDANTS(
        [Customer].[Customer Geography].[City].&[Coffs Harbour]&[NSW],
        [Customer].[Customer Geography].[Customer]
      ),
      (
        measures.customerage
      )
    )
  } ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]  

This will get you:

image

The value for All Periods is correct due to the WHERE statement. With out the WHERE statement you get:

image

Have Fun And Till Next Time

1 comment:

  1. Hi,

    find Age Calculator then it is Overall looking for a Chronological age or anniversary calculator then this is your best option in age Calculator.

    Age Calculator By Date Of Birth (Days, Months)
    Calculation of age with interesting details!
    If you want to know the details of your age, then install the Age Calculator

    ReplyDelete