Friday, July 22, 2016

All member

All member is a very handy member to use in some MDX calculations. Remember, when you drop any measure and no other dimension members, it doesn't mean that they aren't in use. Every cell (tuple) has a coordinate. If you don't select any member from hierarchy it doesn't mean that your current coordinate is unknown, it's .DefaultMember, most likely it's .All member (you can change default member in Attribute properties).

New clients case

In order to count clients who have first order in current period we may use min date of invoice in current period and compare with all over the history min date. If they match, then it's a new client.
SUM(
    [Client].[Client].[Client].Members,
    IIF(
        [Measures].[Invoice First Date] > 0 --check the first invoice this period
        and
        [Measures].[Invoice First Date] = ([Date].[Day].[All],[Date].[Month].[All],[Date].[Year].[All],[Measures].[Invoice First Date]), --check the first invoice all time and compare with current value
        1,
        NULL
    )
)
As you may mention the first month obviously has the biggest value, that's because the start point to record invoices is Oct 2012. Now we have a great tooling to analyze the efficiency of marketing department promotions by comparing previous periods with promotion periods aftereffect.

No comments:

Post a Comment