Finding the number of distinct customers you had in any given time period, and then finding how many of those have bought from us before and how many are new customers, is a pretty common requirement. It’s a problem I blogged about a few months ago and showed how to solve in it DAX; I was thinking about it again recently and have just updated that post with a more elegant solution. However I thought it would also be useful to show how to do these calculations in SSAS and MDX.
Here’s a query on the Adventure Works cube that shows how to calculate these values:
WITH
MEMBER MEASURES.[Returning Customers] AS
COUNT(
NONEMPTY(
NONEMPTY(
[Customer].[Customer].[Customer].MEMBERS
, [Measures].[Internet Sales Amount])
, {[Measures].[Internet Sales Amount]}
* {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}
)
)
MEMBER MEASURES.[New Customers] AS
[Measures].[Customer Count] – MEASURES.[Returning Customers]
SELECT
{[Measures].[Customer Count]
, MEASURES.[Returning Customers]
, MEASURES.[New Customers]} ON 0,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]
There’s already a measure in the cube, [Measures].[Customer Count], that gives the distinct count of customers, so that bit’s easy. Finding the number of returning customers, ie customers who’ve bought something in this time period and who have also bought something from us before, is the tricky bit. It’s a four stage process:
- First, get the set of all customers:
[Customer].[Customer].[Customer].MEMBERS - Then, filter that set to get the set of all customers who bought something in the current time period. Using the NonEmpty function (as opposed to the Filter function) is the most efficient way of doing this – customers who bought something in the current time period are those who have a value for the measure Internet Sales Amount: NONEMPTY(
[Customer].[Customer].[Customer].MEMBERS
, [Measures].[Internet Sales Amount]) - Then, take this set and find the customers in that set that bought something in all time periods previous to the current time period. Again, we can use the NonEmpty function to do this, but this time in the second parameter we want to find the customers who have a value for Internet Sales Amount for the set of time periods {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}; for more information on how NULL and the colon operator is used here, see this post. This gives us the set expression: NONEMPTY(
NONEMPTY(
[Customer].[Customer].[Customer].MEMBERS
, [Measures].[Internet Sales Amount])
, {[Measures].[Internet Sales Amount]}
* {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}
) - Finally, we need to count the number of items in this set using the Count function.
Of course, with the count of distinct customers and the count of returning customers, we can subtract the latter from the former and then get the number of new customers, ie those who’ve never bought anything from us before.
Here are the results from the Adventure Works cube:
UPDATE: if you’re hitting performance problems with this type of calculation, you might also want to read the following post http://blog.crossjoin.co.uk/2013/06/28/optimising-returning-customers-calculations-in-mdx/
very cool Chris! and thanks for the post on the COLON and NULL operators!
Hi Chris,
Another approach to this problem is to use aggregates of the distinct count measure: [Customer Count]. The Customer dimension then need not be included in the calculation:
WITH
MEMBER MEASURES.[Cumulative Customers] AS
Aggregate({Null:[Date].[Calendar].CURRENTMEMBER}
, [Measures].[Customer Count])
MEMBER MEASURES.[Prior Customers] AS
Aggregate({Null:[Date].[Calendar].CURRENTMEMBER.PREVMEMBER}
, [Measures].[Customer Count])
MEMBER MEASURES.[Returning Customers] AS
[Measures].[Customer Count] + MEASURES.[Prior Customers]
– MEASURES.[Cumulative Customers]
MEMBER MEASURES.[New Customers] AS
[Measures].[Customer Count]
– MEASURES.[Returning Customers]
SELECT
{[Measures].[Customer Count]
, MEASURES.[Returning Customers]
, MEASURES.[New Customers]} ON 0,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]
hello sir do you have an idea on calculating the lost customers without using the customer dimension please
i really need your help
thanks in advance
You have to have some way to identify individual customers to calculate lost customers in MDX, and that means you need a customer dimension.
What’s faster:
{NULL:CurrentMember}
or
PeriodsToDate([All], CurrentMember)?
Deepak – good thinking. I wonder which one is faster? I’ll have to do some tests.
QQ – it depends. I suspect that PeriodsToDate may be faster in 2005, but with 2008/R2 there may be no difference.
Hi,
I’m quite new to MDX. And I wonder if it is possible, to create a named set instead of the measure with the count function.
If I had a named set, I could use all the existing measures (sales amount, customer count, …). So I don’t have to define a member for every measure I want to see.
So I tried to delete just the count() part. But that doesn’t seem to work …
Thx for any help on this
No, unfortunately not – a named set is just a list of things, and you can’t return a set from a measure. You need to use a function like count() to derive a value from the set, which you can then return from a measure.
Hi again,
What about LOST CUSTOMERS on a previous period basis.
Again I tried, but once again I failed … 😉
The easy part: previous period count
WITHMEMBER MEASURES.[Returning Customers pp count] ASCOUNT( NONEMPTY( NONEMPTY( [Customer].[Customer].[Customer].MEMBERS , [Measures].[Internet Sales Amount]) , {[Measures].[Internet Sales Amount]} * {[Date].[Calendar].CURRENTMEMBER.PREVMEMBER} –only compared to pp )
)MEMBER MEASURES.[Lost Customers pp count] AS
([Measures].[Customer Count],[Date].[Calendar].CURRENTMEMBER.PREVMEMBER) – (MEASURES.[Returning Customers pp count])
The tricky part: previous period sales
MEMBER MEASURES.[Returning Customers pp sales] ASSUM( NONEMPTY( NONEMPTY( [Customer].[Customer].[Customer].MEMBERS , [Measures].[Internet Sales Amount]) , {[Measures].[Internet Sales Amount]} * {[Date].[Calendar].CURRENTMEMBER.PREVMEMBER} )
,[Measures].[Internet Sales Amount]
)MEMBER MEASURES.[Lost Customers pp sales pp] AS
???
-> set of previous period customers without set of current period customers
-> the sum of previous period sales from this new set of customers
???
Is this logic correct? And how do I get this set of customers, who only bought something in pp and not (yet) in cp?
Thx a lot.
Hi Reto,
To get the ‘lost’ customers you need to find the set of customers who bought something previously, then remove the set of customers who have bought something in this time period. You can use the Except() function for this, like so:
WITH
MEMBER MEASURES.[Lost Customers] AS
COUNT(
EXCEPT(
NONEMPTY(
[Customer].[Customer].[Customer].MEMBERS
, {[Measures].[Internet Sales Amount]}
* {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}
)
,
NONEMPTY(
[Customer].[Customer].[Customer].MEMBERS
, [Measures].[Internet Sales Amount])
)
)
SELECT
{[Measures].[Customer Count]
, MEASURES.[Lost Customers]} ON 0,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]
Thx. The except function was ‘my’ missing part.
Sometimes it’s that easy …
What I need is to find all products that were bought e.g. in the last 20 month but not in the last 3 from my current selected time period (I will use Excel as front end so I need the code for calculations in cube and not in the MMC)
I tried things like replacing
{NULL : [DIM Time].[Calendar].CURRENTMEMBER.PREVMEMBER}
with
{
lastperiods(20,[DIM Time].[Calendar].CURRENTMEMBER) : lastperiods(3,[DIM Time].[Calendar].CURRENTMEMBER)}
But it always ends up in an value error
Any ideas?
You’re getting an error because Lastperiods returns a set, and the range operator needs two members. Try something like this instead:
[DIM Time].[Calendar].CURRENTMEMBER.LAG(20): [DIM Time].[Calendar].CURRENTMEMBER.LAG(4)
HTH,
Chris
hello, how do I see which lost customers, taking the other to see the gains?
Can you explain what you want in a bit more detail please?
Hello
In my project BI in SQL Server (SSAS) I need to find the lost customers and gains in time or even seller over time. My hierarchy is [Time]. [Calendar] … where Calander -> year, semester, quarter, month and day.
So I did as you indicate here for customer gains. I think in my case fits perfectly. However, for customers not lost works well.
Could you help me?
-> Gain Customers:
(
[Measures]. [Customer Count]
–
(
COUNT (
nonempty (
nonempty (
[Entities]. [Entity Number]. [Entity Number]. MEMBERS,
[Measures]. [Sale price])
{[Measures]. [Sale price]} *
{NULL: [Time]. [Calendar]. CURRENTMEMBER.PREVMEMBER}
)
)
)
)
If crossing the seller will give the seller gains by customers, right?
Now how do I get the number of lost customers?
These results should return the number of customers, but how do I know specifically which customers, the NAME?
Sorry my english.
To find out how to calculated lost customers, look at the comment above in reply to Reto E.
Only works for the year.
For the customer gains, the MDX query, checks on the period or the period imediantamente counterpart before.
example:
Year 2012 -> Year 2011 or January 2012 -> January 2012
or
January 2012 -> February 2012
How do I do?
I’m sorry, I don’t understand what you mean. Can you give an example?
Making their way to get lost customers, so it works for the year, the rest of the hierarchy gives very high values. For example, in 2012-33, down in the hierarchy, the 1st half of the 65th – 2nd half of 77 … and so on.
How to get to work … can you explain the operation?
and how to get customers which, unlike the number of customers lost / won?
Thanks Chris, this works great!
Now I’m only searching for 2 more measures linked to this. I want to see the number of Winners and the number of Losers for a given period. So effectively the number of returning customers would be split in the number of customers who have bought more than in the previous period and the number of customers who have bought less (not nothing) than they did in the previous period.
Rgds, Martijn
Hi Martijn,
Something like this should do what you want:
WITH
MEMBER MEASURES.[Winners] AS
COUNT(
FILTER(
NONEMPTY(
NONEMPTY(
[Customer].[Customer].[Customer].MEMBERS
, [Measures].[Internet Sales Amount])
, {([Measures].[Internet Sales Amount]
,[Date].[Calendar].CURRENTMEMBER.PREVMEMBER)})
, [Measures].[Internet Sales Amount]>
([Measures].[Internet Sales Amount]
,[Date].[Calendar].CURRENTMEMBER.PREVMEMBER)
)
)
SELECT
{[Measures].[Winners]} ON 0,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]
Here, the Winners measure finds the number of customers who have bought something in the current time period and the previous time period, and who bought more in the current time period than in the previous time period.
Works like a charm! Thanks for your fast response.
Hi Chris, found your post and it fits very well with the problem I’m facing now.
Let’s say you want to get the Internet Sales Amount for the Returning Customers. I have used your code to create a calculated set in the cube and then use this to aggregate with Internet Sales, but the value returned is NULL (empty / blank) for every Calendar period. Don’t know what I’m missing.
can you post your code?
For the set:
CREATE DYNAMIC SET CURRENTCUBE.[Returning Customers]
AS NONEMPTY(
NONEMPTY(
[Customer].[Customer].[Customer].MEMBERS
, [Measures].[Internet Sales Amount])
, {[Measures].[Internet Sales Amount]}
* {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}
), DISPLAY_FOLDER = ‘Sets’ ;
For the calculated member:
CREATE MEMBER CURRENTCUBE.[Measures].[Returning Customer Sales]
AS AGGREGATE ([Returning Customers],[Measures].[Internet Sales Amount]),
FORMAT_STRING = “Currency”,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’ ;
The problem here is that you can’t use a named set like this. A named set, even if it is marked as dynamic, will only ever be evaluated once per query – you need to insert the set expression directly inside your calculated measure, so it gets evaluated each time the calculation gets called (as I’ve done in the post).
Ok, thanks for your response
Hi Chris,
I am working on a slightly different scenario than the one who explained above;
is it possible to show the returning customer for every time period? For example users want to know the returning customers in Dec who also purchased in August, etc. It is possible to modify your code to answer this specific question but can this be made more generic calculated member so users can see across time period who the returning customers are.
Thanks,
Vamsi.
Yes, it’s certainly possible to show the returning customers but you wouldn’t use a calculated member for this. Instead of counting the number of items in the set, you display the set’s contents on the rows axis of the query. So something like this (I don’t have access to SSAS at the moment so I can’t test the query, but you get the idea) which shows the list of customers who bought in 2004 and all years before:
SELECT
{[Measures].[Internet Sales Amount]} ON 0,
NONEMPTY(
NONEMPTY(
[Customer].[Customer].[Customer].MEMBERS
, ([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2004]))
, {[Measures].[Internet Sales Amount]} * {NULL : [Date].[Calendar Year].&[2003]})
ON 1
FROM [Adventure Works]
Chris,
Thanks for the fast response. I am sorry I was not clear in my problem description.
your query works and it brings out the customers who are returning (from beginning of time to 2003) customers for 2004. Is it possible to break this returning customers for 2004 into each year;
customers who are returning in 2004 and made purchases in 2003
customers who are returning in 2004 and made purchases in 2002
customers who are returning in 2004 and made purchases in 2001
customers who are returning in 2004 and made purchases in 2000
etc..to beginning of time.
this could mean that the same customer can repeat multiple times if they made purchases in multiple years.
Thanks,
Vamsi.
So, on rows, you’d want to see a list of years and then crossjoined with each year you’d see a list of the customers who made purchases in that year and are returning in 2004?
Yes.
Here you go:
SELECT
{[Measures].[Internet Sales Amount]} ON 0,
GENERATE(
[Date].[Calendar Year].[Calendar Year].MEMBERS
,
{[Date].[Calendar Year].CURRENTMEMBER}
*
NONEMPTY(
NONEMPTY(
[Customer].[Customer].[Customer].MEMBERS
, ([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2004]))
, ([Measures].[Internet Sales Amount]))
)
ON 1
FROM [Adventure Works]
Hi Chris,
(sorry for my poor english)
I’ve got a similar scenario for as customer of us who wants to get insights in new, returning en lost customers i a selected period. However, this customer browse the cube with excel 2010. Is it possible to configure the cube where you count for example the lost customers for a selected period, and then put the customer City on the rows?
When i try to make calculated measures of above suggestions, then there is no relation with the customer dimension.
Do you have any suggestions which lead me to the right approach?
Rgds, Wilfred
add Existing before .Members
You’d need to do something like create two extra time dimensions (with no relationship with any measure group) so users could define their selected period for comparison, and then reference that selection inside your calculation. It’s feasible but I can’t think of an example online I can reference easily.
Hi Chris,
Thanks for above demo. I have a question. I am trying to create a calculated measure that display count of Customer that but Product A and Product B. So I created the a measure A and Measure B (Sales Amount). When I add the both measures together this will give me the list of customers with there sales amount. Instead of displaying sales amount I want to display 1. So when I browse the cube It will roll up. Below is the current code I am using
with
member measures.[A] as
(
[Product].[Product Categories].[Product].[Mountain-200 Silver, 38],
[Measures].[Internet Sales Amount]
)
member measures.[B] as
(
[Product].[Product Categories].[Product].[Mountain-200 Black, 42],
[Measures].[Internet Sales Amount]
)
member measures.[C] as
measures.[A] + measures.[B]
member measures.[D] as
count (
nonempty (
[Customer].[Customer].[Customer].members,
measures.[C]
)
)
//Mountain-200 Black, 42
select
//nonempty (Measures].[Internet Sales Amount], measures.A, measures.B, measures.C,
{ measures.D}
//)
on 0,
non empty (
{[Date].[Calendar Year].members}
//{[Customer].[Customer].[Customer].members}
)
on 1
from [Adventure Works]
The above code works well if I don’t display customer on row axis. But I will like to customer on the row too (the query is very slow and it display the same value for all the customers in mine dimension).
Please do you have any suggestion.
Thanks,
Femi
Hi Femi,
Does this version of measures.D do what you want?
member measures.[D] as
count (
nonempty (
existing [Customer].[Customer].[Customer].members,
–measures.[C]
{[Measures].[Internet Sales Amount]} *
{[Product].[Product Categories].[Product].[Mountain-200 Silver, 38],
[Product].[Product Categories].[Product].[Mountain-200 Black, 42]}
)
)
Hi Chris
Thanks for your post, It has been very helpful.
I’m currently working on slightly similar case like the one that you have described in your post.
What I need to archive is to create new calculated measures based on the status of Contract. The status attribute can change within a month and the contract dimension is SCD type II. Contract dimension table has start and end dates for tracking the attribute changes.
I need to be able to report how many Contracts are open, new or closed based on the status of the contract within time. Reporting happens at month level so I need to report my Contracts based on the status at the end of each the month. Also I need to report the number of contracts that have bought something from us based on the sales measure in month level (Active contracts and Passive contracts)
Any suggestions?
Best Regards, Jask ma
It sounds like you would need to create a few new measure groups on your dimension table, and use the techniques described in this post to find out the status of each invoice at any point in time: http://cwebbbi.wordpress.com/2011/01/21/solving-the-events-in-progress-problem-in-mdx-part-1/
Hi Chris,
I’m pretty new to MDX. I saw one of your post back on Nov. 28, 2011 about returning customer for every time period. My scenario is to return any customer who purchased in previous year, but did not purchase current year. I tried to test your code and use except and isempty, but not successful. I would be greatly appreciated if you can modify the following code that you posted last year to show customers who did not purchase in 2004.
SELECT
{[Measures].[Internet Sales Amount]} ON 0,
NONEMPTY(
NONEMPTY(
[Customer].[Customer].[Customer].MEMBERS
, ([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2004]))
, {[Measures].[Internet Sales Amount]} * {NULL : [Date].[Calendar Year].&[2003]})
ON 1
FROM [Adventure Works]
Thanks,
Jocelyn Lau
Here you go, this returns customers who bought in 2003 but not in 2004:
SELECT
{[Measures].[Internet Sales Amount]} ON 0,
EXCEPT(
NONEMPTY(
[Customer].[Customer].[Customer].MEMBERS
, ([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2003]))
,
NONEMPTY(
[Customer].[Customer].[Customer].MEMBERS
, ([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2004]))
)
ON 1
FROM [Adventure Works]
Chris,
Thank you so much for your help!
Jocelyn Lau
Hi Chris,
Hope you’re well. Very cool blog! I’ll need to spend alot of time reading some of the articles. I’ve only started using SSAS this year, so may be a little green in some areas. I’ve managed to create a cube and put some calculations in it, but the one that I am struggling with is a the sum of the customer count over a period of time that counts only distinct customers. I have a look at your above and have tried to get it to work for me with minimal luck.
Just the first question as this seems to be something that I might have just missed the point on, but this section of code that you have (which I’ve adapted slightly for my project), does it go in the Calculations as calculated member?
I have the below in my cube from your example
CREATE MEMBER CURRENTCUBE.[Measures].[TestCustomerCount]
AS
DISTINCTCOUNT([Customers].[Customer Code].[Customer Code].Members),
VISIBLE = 1, DISPLAY_FOLDER = ‘Test’;
WITH
MEMBER [MEASURES].[Returning Customers] AS
COUNT(
NONEMPTY(
NONEMPTY(
[Customers].[Customer Code].[Customer Code].MEMBERS
, [Measures].[Value USD])
, {[Measures].[Value USD]}
* {NULL : [Dates].[CalendarHeirarchy].CURRENTMEMBER.PREVMEMBER}
)
)
MEMBER MEASURES.[New Customers] AS
[Measures].[TestCustomerCount] – [MEASURES].[Returning Customers]
SELECT
{[Measures].[Customer Count]
, MEASURES.[Returning Customers]
, MEASURES.[New Customers]} ON 0,
[Date].[CalendarHeirarchy].[Date Year].MEMBERS ON 1
FROM [TestDatabase];
If I process my cube I get – Error 8 Parser: The script contains the statement, which is not allowed.
Then the main reason that I am trying all this is that I need to get a list of the distinct customers that purchased over a 12mm moving period, I seem to have sorted out the 12mm using the Time Intelligence Wizard, which works on all my other calculated members, but not on this one as it’s not additive.
To go over a simple situation, if the following customers purchased in the following months:
Jan 12 – A, B, C, D, E (5 customers)
Feb 12 – A, B (2 customers)
Mar 12 – E, F, G (3 customers)
Now I want a calculated member that will be able to return 7 (i.e. there were 7 distinct customers in the period, A, B, C, D, E, F, G)
Thanks for your time and effort, and congrats on becoming an SSAS Maestro (may be a delayed congrats)
Regards
Mark
I see a syntax error in the code that I put up there, in the bottom SELECT statement I used [Measures].[Customer Count] instead of [Measures].[TestCustomerCount]. I’ve made the update here, but there is still the same error when processing.
Hi Mark,
In your example, everything from WITH MEMBER to the end is an MDX query, so it is something that can only be executed in SQL Server Management Studio (or via code). It’s only the CREATE MEMBER statement before it that can go on the Calculations tab on the cube.
Chris
Hi Chris,
Thanks for your feedback and explaining which parts of it were MDX and which were in the Calculations tab. I’ve got that setup now and can run the query which returns the results.
This wasn’t really what I was aiming to achieve, but was some extra insight into SSAS. I’m still stuck with my last question of the distinct customers. This also preferably needs to be done in the calculations tab, as we’re delivering this onto an XLS sheet which the calculations can be dragged nicely onto the pivot table where the MDX is abit of a problem to get on there.
I think one of the main issues that I have with the distinct customers is that I can get a list of the distinct customers easily which is sliced by a selection period that is selected as the filter, however the 12 month moving thing that I have created is as per below
CREATE MEMBER CURRENTCUBE.[Dates].[12mm].[12mm LY] AS “NA” ;
// 12mm LY
(
[Dates].[12mm].[12mm LY],
[Dates].[MMM YY].[MMM YY].Members,
[Dates].[Date ID].Members,
{
[Measures].[AverageExchangeRate],
[Measures].[AverageRSP-LC],
[Measures].[AverageRSP-USD],
[Measures].[AverageOrderValue-LC],
[Measures].[AverageOrderValue-USD],
[Measures].[TotalProductCount],
[Measures].[ProductCount],
[Measures].[ProductCountPercent],
[Measures].[AverageOrderUnits],
[Measures].[ProductAvailability],
[Measures].[CustomerSalesRatioToParent-LC],
[Measures].[CustomerSalesRatioToParent-USD],
[Measures].[CustomerSalesRatioToParent-Units],
[Measures].[CustomerSalesRatioToParent-Tons],
[Measures].[CustomerSalesRatioToParent-KGs],
[Measures].[ProductSalesRatioToParent-LC],
[Measures].[ProductSalesRatioToParent-USD],
[Measures].[ProductSalesRatioToParent-Units],
[Measures].[ProductSalesRatioToParent-Tons],
[Measures].[ProductSalesRatioToParent-KGs],
[Measures].[TotalCustomerCount],
[Measures].[CustomerCount],
[Measures].[CustomerCountPercent],
[Measures].[Units],
[Measures].[Value LC],
[Measures].[Value USD],
[Measures].[KGs],
[Measures].[Tons],
[Measures].[Orders],
[Measures].[PricePerTon-LC],
[Measures].[PricePerTon-USD]
}
)
=
AGGREGATE(
ParallelPeriod(
[Dates].[CalendarHeirarchy].[MMM YY],
23,
[Dates].[CalendarHeirarchy].CurrentMember
)
:
ParallelPeriod(
[Dates].[CalendarHeirarchy].[MMM YY],
12,
[Dates].[CalendarHeirarchy].CurrentMember
)
*
[Dates].[12mm].[Current Dates]
) ;
CREATE MEMBER CURRENTCUBE.[Dates].[12mm].[12mm Growth]
AS ([Dates].[12mm].[12mm CY] – [Dates].[12mm].[12mm LY]) / [Dates].[12mm].[12mm LY] ;
// YearAgoMonth
(
[Dates].[12mm].[YearAgoMonth],
[Dates].[MMM YY].[MMM YY].Members,
[Dates].[Date ID].Members,
{
[Measures].[AverageExchangeRate],
[Measures].[AverageRSP-LC],
[Measures].[AverageRSP-USD],
[Measures].[AverageOrderValue-LC],
[Measures].[AverageOrderValue-USD],
[Measures].[TotalProductCount],
[Measures].[ProductCount], <- doesn't work
[Measures].[CustomerCount], <- doesn't work
}
)
=
AGGREGATE(
ParallelPeriod(
[Dates].[CalendarHeirarchy].[MMM YY],
12,
[Dates].[CalendarHeirarchy].CurrentMember
)
:
ParallelPeriod(
[Dates].[CalendarHeirarchy].[MMM YY],
12,
[Dates].[CalendarHeirarchy].CurrentMember
)
*
[Dates].[12mm].[Current Dates]
) ;
Hi Mark,
How are you currently calculating distinct customers? Are you using a distinct count measure (as described here for example: http://svangasql.wordpress.com/2012/03/14/distinct-count-in-analysis-services/) ? That’s going to be the easiest way of doing it, and should work with the calculation above.
Chris
Hi Chris,
I’m new to mdx and I have a problem similar to the lost customers query you have demonstrated.
Here is my scenario. I have a cube designed to track the hiring process of an applicant for a job. So, they go from applying, to hired, and sometimes to termination.
I want to find the number of employees that are hired in a particular month and see how many of these same employees are still around in each month after.
So 10 employees were hired in July 2012, I want to get the number that have not been terminated in August 2012 and so forth.
July 2012 Hired August 2012 Sept 2012
10 9 remain 7 remain
My measures are [Hired Qty] and [Termination Qty].
I have role playing dimensions for date: [Hire Date] and [Termination Date].
I was thinking I need to get the Hired Month as a set and then subtract out the Termination Qty for each month after by the Termination Date dimension.
Thanks.
Brock
Hi Brock,
This is going to be more of a cube design problem than an MDX problem: once you’ve designed your cube appropriately the MDX should be straightforward (and conversely, the MDX will be horrible if your cube isn’t designed appropriately!). Can you give me some idea of what your fact table looks like?
Chris
Sure, the fact table is setup as an accumulating snapshot of the hiring process for an employee.
CREATE TABLE [Hiring].[factCandidatePipeline](
[dimCandidateKey] [int] NOT NULL,
[dimEmployeeKey] [int] NOT NULL,
[dimApplicationStartDateKey] [int] NOT NULL,
[dimReviewedDateKey] [int] NOT NULL,
[dimInterviewDateKey] [int] NOT NULL,
[dimNotInterestedDateKey] [int] NOT NULL,
[dimBackgroundCheckRequestDateKey] [int] NOT NULL,
[dimBackgroundCheckResponseDateKey] [int] NOT NULL,
[dimHiredDateKey] [int] NOT NULL,
[dimEmployeeStartDateKey] [int] NOT NULL,
[dimTerminationDateKey] [int] NOT NULL,
[dimOnboardingStartDateKey] [int] NOT NULL,
[dimOnboardingCompleteDateKey] [int] NOT NULL,
[ApplicantQty] [tinyint] NOT NULL,
[ApplicationCompletedQty] [tinyint] NOT NULL,
[HiredQty] [tinyint] NOT NULL,
[ApplyHiredLagDays] [int] NULL,
[TerminationQty] [tinyint] NOT NULL,
[HiredTerminationLagDays] [int] NULL,
[BackgroundCheckRequestQty] [tinyint] NOT NULL,
[ApplyBackgroundCheckRequestLagDays] [int] NULL,
[BackgroundCheckResponseQty] [tinyint] NOT NULL,
[BackgroundCheckRequestResponseLagDays] [int] NULL,
[OnboardingStartQty] [tinyint] NOT NULL,
[ApplyOnboardingStartLagDays] [int] NULL,
[OnboardingCompleteQty] [tinyint] NOT NULL,
[ApplyOnboardingCompleteLagDays] [int] NULL,
[EmployeeStartOnboardingCompleteLagDays] [int] NULL,
[ReviewedQty] [tinyint] NOT NULL,
[ApplyReviewedLagDays] [int] NULL,
[InterviewQty] [tinyint] NOT NULL,
[ApplyInterviewLagDays] [int] NULL,
[NotInterestedQty] [tinyint] NOT NULL,
[ApplyNotInterestedLagDays] [int] NULL,
[DropOffQty] [tinyint] NOT NULL)
OK, in that case it should be fairly straightforward. You create a calculated measure that takes the number of hires measure for all termination dates, and then subtracts the sum of terminations from the beginning of time up to the current termination date. You can then select your hire date and then put termination date on rows or columns and see how the number of hires still employed reduces over time. The MDX would be something like this:
(Measures.[Hires], [Termination Date].[Calendar].[All]) – SUM(null:[Termination Date].[Calendar].currentmember, Measures.[Terminations])
Chris
This doesn’t seem to give me what I want. I want to take the employees that were hired in January 2012 and see how many of those same employees from January 2012 remain in February 2012.
When you said take “select your hired date”, did you mean to put hire date on one axis and the terminated date on the other axis?
Sorry not to be clear – I meant to say you could put Hire Date on the filter axis and then put Termination Date on rows or columns; this should ensure you slice so that you only get the people hired in a particular month.
Thanks, this seems to be working when browsing with Excel when I pick 1 month of the Hire Date calendar. I have a couple of questions though.
Why does this not work when browsing with SSMS?
Also, do you think it would be possible to put the Hire Date dimension on 1 axis and the Termination Date on another axis so I can see this for multiple Hire date months?
Thanks a bunch Chris. Putting Hire Date on X axis and Termination Date the Y axis works in Excel. I’ve been trying to figure this out for 2 weeks.
I still don’t understand why the calculation doesn’t work in SSMS.
Since we will be using Excel for our front end I guess we don’t have a big problem.
It might not be working in SSMS because you’re putting the Hire Date dimension in the filter pane at the very top; it should work if you drag it into the relatively think section marked (from memory) ‘Drop Filter Fields Here’.
Thanks, will give this a try.
Hi,
I’m struggling with a slightly different problem and I’m stuck, so I’m searching for some advises. I have customers dimension, statuses dimension (let’s put for simplicity just 2 statuses) and dates. In fact table I keep history of status changes for every customer, so, for example, when a customer enters the database he/she is in “status_1”, after some time, the customer may change his status to “status_2” and I add one more record to fact table with date; and after that customer may again return to “status_1” – one more record in fact table again. These “jumps” between statuses occur not often than once a day per customer.
Now, I cannot figure out how to do a report about customers database with respect to latest known status for a given date.
Suppose we have only one customer in our database. 2012-01-01 this customer was in “status_1”, so we have one record in fact table; 2012-03-01 he changed status to “status_2” – we add another record; and, finally, 2012-05-01 he moved back to “status_1”. I do a report, and my reporting date is 2012-02-01. I see 1 customer in my database in “status_1”, 0 customers in “status_2”. When my reporting date is 2012-04-01, then I see 0 customers for “status_1” and 1 customer in “status_2”. And, when report is done for 2012-06-01, I again see 1 customer in “status_1” and 0 customers in “status_2”.
With pure SQL and given data model I would solve the problem with few lines of code. like
SELECT
t.[last_status]
,COUNT(t.[customer_id])
FROM (
SELECT
r.[customer_id]
,(SELECT TOP 1 l.[status_id]
FROM [dwh].[dbo].[fact_customers_statuses] l
WHERE l.[customer_id] = r.[customer_id] AND l.status_id IN (1, 2) AND l.status_day_date < '2012-02-01'
ORDER BY l.status_day_date DESC) AS [last_status]
FROM (
SELECT DISTINCT
f.[customer_id]
FROM [dwh].[dbo].[fact_customers_statuses] f
WHERE
f.status_day_date <= '2012-02-01' AND f.status_id IN (1, 2)) AS r) AS t
WHERE
t.[last_status] IS NOT NULL
GROUP BY
t.[last_status]
Works in less than a second!
I have no idea how to do equivalent in MDX. the task seems to be simple, but it's not. At least for me.
What I got so far, is I can obtain the latest status per each customer for a given date (t query in my SQL code). It is extremely slow – 5 minutes.
WITH
Member Measures.[Last Status] AS
NonEmpty
([Customer Statuses].[Id].[Id]
,(Tail(NonEmpty({NULL:Tail(Descendants([Customer Statuses Dates].[Day Date].CurrentMember, [Customer Statuses Dates].[Day Date].[Day Date])).Item(0)}
,[Measures].[Customers Count]
)),[Measures].[Customers Count]
)).Item(0).Name
SELECT
NON EMPTY [Customer Statuses Dates].[Day Date].&[2012-01-01T00:00:00] * {Measures.[Last Status]} ON 0,
NON EMPTY {[Customers].[Id].[Id]} on 1
FROM [Applications]
And I do not know what to do next. How to perform counts now?
Please, help!
Hi Oleksandr,
This is exactly the scenario I’m describing in this blog post. Instead of treating Status like a dimension, treat it like a measure (ie return the last non-empty Status key value) and the technique above will work. All you need to do then is use another calculated member to translate the key value into a readable status.
Chris
Hi Chris,
Yes, I understand the idea. My problem is that I know what to do, but I do not know how. I come from pure SQL world, and there are no “attributes” or “measures” entities, because everything can be turned into kind of attribute or measure. For me SSAS is a bit “black and white” yet, but I think it’s a matter of experience and understanding what is going on behind the scene. I’ll keep trying, also with another your post about Last ever non empty, which might be useful in this case as well.
Following your post about Last ever non empty, I created a fake measure [Maximum Date] in my fact table. then I added script to my cube as following
CREATE MEMBER CURRENTCUBE.[Measures].[Days To Date]
AS COUNT(NULL:[Customer Statuses Dates].[Day Date].CurrentMember) – 1,
VISIBLE = 0;
CREATE MEMBER CURRENTCUBE.[Measures].[Customer Had Status]
AS IIF([Measures].[Customers Count] = 0, NULL, [Measures].[Days To Date]),
VISIBLE = 0;
//—-
SCOPE([Measures].[Maximum Date]
, [Customer Statuses Dates].[Day Date].[Day Date].Members
);
This = MAX(
{NULL:[Customer Statuses Dates].[Day Date].CurrentMember}
, [Measures].[Customer Had Status]);
END SCOPE;
I also added [Last Status Id] measure to my fact table (It could be Max or Sum, doesn’t matter). Now, combining [Maximum Date] and [Last Status Id] I can get LAST_AVAILABLE_STATUS for any reporting date for any existing on that date customer like following
MEMBER MEASURES.LAST_AVAILABLE_STATUS AS
IIF(ISEMPTY(MEASURES.MAXDATE), NULL,
([Measures].[Last Status Id], [Customer Statuses Dates].[Day Date].[Day Date].MEMBERS.ITEM(MEASURES.[Maximum Date])))
How do I perform count of the customers by this last available status?
Can you tell me what you mean when you say you want the count of customers by last available status? Does the Last Available Status measure return a key value of some sort?
Yes, this is “status_id” key. Now, I want to count the customers according to this “status_id” key, and group them by status name.
Actually, I solved this problem by another way, by augmenting my fact table. Every time a customer changes status, I add record like “day_date:2012-03-01, customer_id:1, status_id:2, status_sign:1”. At the same time I add record to denote that a customer changed status from 1 to 2 by adding a record “day_date:2012-03-01, customer_id:1, status_id:1, status_sign:-1”. Then, my measure is just a simple sum of “status_sign” and that’s it!
That sounds like a better solution – any MDX approach is always going to perform worse than this.
Yes, speed is awesome! anyway, thank you for your time! Happy New Year!
Hi All
Can anyone help me out in this.
In my database i have stored value for new customer in cache table using below query.
But i want to do the same in SSAS without using cache table.
Here senario is i want count of customers present in current date but not present in previous date(New customers).
For e.g. I want count of customers present in ‘2013-01-05’ but not present in ‘2013-01-04’ then present in ‘2013-01-04’ but not in ‘2013-01-03’ so on…
insert into tb_Date
select distinct Date,Freq_ID from Tb_Customer
order by Date desc
Declare @count int,@CountEnd int
set @count = 1
set @CountEnd = (select count (1) from tb_Date)
Declare @currnt_date datetime
Declare @compr_date datetime
while (@count < @CountEnd)
begin
set @currnt_date = (select Date from tb_Date where ID = @count )
set @compr_date = (select top 1 Date from tb_Date where Date < @currnt_date)
insert into New_Customer_Count
select COUNT(Distinct Customer_id) as Total_Count
from Tb_Customer A
where Date = @currnt_date
and Customer_id not in
(
select Customer_id from Tb_Customer B
where Date = @compr_date
)
set @count = @count + 1
end
thanks in advance
Regards,
Swapnil
Hi Swapnil,
Read the blog post, that’s the problem it explains how to solve…!
Chris
Hi Chris,
I read your blog.
But here what i need is count of customer present for a date but not present in last previous date i.e. (date – 1)…I have done this using MDX after reading your blog thanks for the same.
But my query is too slow when i take date range for 1 year..so need to cache new customers as measure in SSAS.
Please help in this as i am trying this from long time.
Thanks,
Swapnil
Hi Chris,
Below is the MDX for the same.
WITH
MEMBER [MEASURES].[Returning Customers] AS
COUNT(
NONEMPTY(
NONEMPTY(
[Tb Customers].[Customer ID].members
,[Measures].[Customers Count] )
, {[Measures].[Customers Count]}
* {NULL : [Time].[Date].currentmember.PREVMEMBER}
)
)
MEMBER [MEASURES].[New Customers] AS
[Measures].[Customers Count] – [MEASURES].[Returning Customers]
SELECT
{[Measures].[Customers Count]
, [MEASURES].[Returning Customers]
, [MEASURES].[New Customers]} ON 0,
{[Time].[Date].&[2012-11-22T00:00:00]:[Time].[Date]..&[2011-11-26T00:00:00]}ON 1
FROM [Cube Splitting]
where [Tb freq].[Freq ID].&[1]
Regards,
Swapnil
I suggest you take a look at the first comment on this post, from Deepak – that contains a different approach that may perform faster for you.
Hi Chris,
Approach given in Deepak comment is considering current date with all previous date data..here i need to consider only difference of one day…i.e. customers present for today but not present on yesterday then customer present yesterday but not present day before yesterday and so on…
Regards,
Swapnil
Here’s Deepak’s query adapted to show new customers on a daily basis:
WITH
MEMBER MEASURES.[Customers Today and Yesterday] AS
Aggregate({[Date].[Calendar].CURRENTMEMBER.PREVMEMBER:[Date].[Calendar].CURRENTMEMBER}
, [Measures].[Customer Count])
MEMBER MEASURES.[Customers Yesterday] AS
([Date].[Calendar].CURRENTMEMBER.PREVMEMBER, [Measures].[Customer Count])
MEMBER MEASURES.[New Customers] AS
MEASURES.[Customers Today and Yesterday]
–
MEASURES.[Customers Yesterday]
SELECT
{[Measures].[Customer Count]
, MEASURES.[Customers Yesterday]
, MEASURES.[Customers Today and Yesterday]
, MEASURES.[New Customers]} ON 0,
FILTER(
[Date].[Calendar].[Date].MEMBERS
, [Measures].[Customer Count]MEASURES.[New Customers])
ON 1
FROM [Adventure Works]
Hi Chris,
Thank you for your help.
Regards,
Swapnil
I’ve tried, unsuccessfully, to modify this to do what I’m looking for: show the number of “consistent” customers from “the start of time” until now. Consistent would mean that they have purchased in each of the time periods until now. Logically, the value could not exceed the max of the number of customers that purchased in the first time period. I think the listed example does this, but only for pairs of periods: “how many bought in this period who also bought in the last period”. Is there a way to get what I’m looking for? All of the approaches that I have considered lead me to guess that I need something recursive, but I suspect that isn’t the MDX way.
I need to rephrase this as I think about what I want vs. what is posted, vs. what I was *incorrectly* asking for. I do need “consistent” but really, it is defined as: how many bought something this month, and in all the N months preceeding this month. Sorry to be unclear, I asked before fully thinking it through.
Hi Sohrab,
That’s an interesting question! I’ve just written a blog post about how to answer it: http://cwebbbi.wordpress.com/2013/02/24/counting-consistent-customers-in-mdx/
Chris
HI Chris,
Can you please help me out in writing MDX query for self join.
Below is my scenario
Here i need only those records which matches with ‘SKU’ of Owner ‘A’.
SQL query for the same is:
Select * from table_A Z
inner join (select SKU from table_A where Owner = ‘A’) M
on Z.sku = M.sku
Table A
ID Product_ID Owner SKU
1 123 A 12345
2 23 A 22222
3 324 A 9999
4 423 B 12345
5 324 B 9999
6 3 B 34456
7 4 B 54667
8 2 C 75675
9 3 C 12345
10 4 C 22222
OUTPUT
ID Product_ID Owner SKU
1 123 A 12345
2 23 A 22222
3 324 A 9999
4 423 B 12345
5 324 B 9999
9 3 C 12345
10 4 C 22222
Thanks,
Swapnil
HI Chris,
Can you please help me out in writing MDX query for self join.
Below is my scenario
Here i need only those records which matches with ‘SKU’ of Owner ‘A’.
SQL query for the same is:
Select * from table_A Z
inner join (select SKU from table_A where Owner = ‘A’) M
on Z.sku = M.sku
Table A
ID Product_ID Owner SKU
1 123 A 12345
2 23 A 22222
3 324 A 9999
4 423 B 12345
5 324 B 9999
6 3 B 34456
7 4 B 54667
8 2 C 75675
9 3 C 12345
10 4 C 22222
OUTPUT
ID Product_ID Owner SKU
1 123 A 12345
2 23 A 22222
3 324 A 9999
4 423 B 12345
5 324 B 9999
9 3 C 12345
10 4 C 22222
Thanks,
Swapnil
Hi Swapnil,
For general questions like this that aren’t related to a specific post, you’re better off posting your question on the MSDN forum (http://social.msdn.microsoft.com/forums/en-us/sqlanalysisservices/threads/).
To answer your question though, here’s an example from Adventure Works. To start with, here’s a query that shows the sales of all products (to all customers) that have been bought by the customer Aaron A. Hayes:
select {[Measures].[Internet Sales Amount]} on 0,
nonempty(
[Product].[Product].[Product].members
, {([Measures].[Internet Sales Amount]
, [Customer].[Customer].&[15568])})
on 1
from [Adventure Works]
Here’s what I think you want though, a query that shows the names of all the customers who bought the same products as Aaron A. Hayes:
select {[Measures].[Internet Sales Amount]} on 0,
non empty
nonempty(
[Product].[Product].[Product].members
, {([Measures].[Internet Sales Amount]
, [Customer].[Customer].&[15568])})
*
[Customer].[Customer].[Customer].members
on 1
from [Adventure Works]
Hi Chirs,
Sure. Next time onwords i willl post new topics in msdn.
Here is my aproch.
with
set [aa] as
filter(
except([Product].[sku].members,[Product].[sku].[all]),
[customer].[customerId].&[2])*[Measures].[Internet Sales Amount]
SELECT
NON EMPTY (NONEMPTY({[customer].[customer Name].&[A]})) on columns,
NON EMPTY(NONEMPTY({[aa]})) ON rows
FROM [cube]
WHERE
(
{[city].[city id].&[1]}
)
Above MDx gives me correct out put as below.
A
13186139 Internet Sales Amount 182.91
13189678 Internet Sales Amount 180.07
13348448 Internet Sales Amount 578.11
13348695 Internet Sales Amount 1349.95
13348696 Internet Sales Amount 1596.51
13442983 Internet Sales Amount 44.99
13721556 Internet Sales Amount 11.99
13818055 Internet Sales Amount 733.22
13818368 Internet Sales Amount 810.59
13818370 Internet Sales Amount 826.72
13818376 Internet Sales Amount 1526.86
13818391 Internet Sales Amount 744.58
13818393 Internet Sales Amount 763.86
13818394 Internet Sales Amount 1041.76
But what i need is to sum (Internet Sales Amount) out of the select statement only and the display the records.
Correct output will be:
A
Internet Sales Amount 10209.21
hi ;
my mdx query not worked ,i want a output Count_enqueteur in the column and connexion_soned,
connexion_onas ,connexion_steg,satisfaction_logement,gouvernorat in row, but don’t now wat is the problem he desplayed me:
” [Measures].[Count_enqueteur]’ not found in cube ‘Analyse’ ”
note please: the measure Count_enqueteur is created.
SELECT
{[Measures].[Count_enqueteur]} on COLUMNS,
{connexion_soned.children,
connexion_onas.children ,
connexion_steg.children,
satisfaction_logement.children,
gouvernorat.children} on rows
FROM Analyse
can you give me a help.
Are you connected to the right database in your MDX query window? Can you try dragging the measure from the metadata pane in the MDX query window into the query pane and seeing if the uniquename that appears is the same as the one you’re using?
Tangential question:
How can I calculated the #unique customers who placed orders based on their status?
e.g. my Customers table/dimension has an Account Status field with values ‘New’ or ‘Verified’ (amongst others)
my distinct count shows the #unique customers who placed orders, but I want to know how many unique ‘Verified’ customers placed orders, and similarly how many ‘New’ customers placed orders?
(as a calculated member in my cube – the example below is just an mdx query to test)
I tried doing something like this but I get an infinite recursion detected:
WITH MEMBER [Measures].[UniqueVerifiedCustomers]
AS Count(nonempty(filter([Customers].[Customer Id].[Customer Id], [Customers].[Account Status] = “Verified”),
[Measures].[#Orders])),
VISIBLE = 1
SELECT
{
[Measures].[UniqueVerifiedCustomers],
[Measures].[#UniqueCustomers], — this is my distinct count on orders’ customerid
[Measures].[#Orders] — count of rows
} on columns
FROM [Orders]
Sorry for the late reply… What you need is a tuple in your calculated member, something like this:
WITH MEMBER [Measures].[UniqueVerifiedCustomers]
AS ([Customers].[Account Status].&[Verified],
[Measures].[#UniqueCustomers])
SELECT …
This is fantastic, thank you!!!
Very neat .impressive.
Chris,
Excellent post. I am still fairly new to MDX and am trying to get a sum of sales from new customers. I can successfully make your example work in my data cube to get the number of new customers but cant seem to get the sum of their purchases. Could you shed a little light on that for me?
Thanks
Michael
Hi Michael,
Does this help?
WITH
MEMBER MEASURES.[Returning Customers] AS
SUM(
NONEMPTY(
NONEMPTY(
[Customer].[Customer].[Customer].MEMBERS
, [Measures].[Internet Sales Amount])
, {[Measures].[Internet Sales Amount]}
* {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}
)
, [Measures].[Internet Sales Amount])
MEMBER MEASURES.[New Customers] AS
[Measures].[Internet Sales Amount] – MEASURES.[Returning Customers]
SELECT
{[Measures].[Internet Sales Amount]
, MEASURES.[Returning Customers]
, MEASURES.[New Customers]} ON 0,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]
Hi Chris
I have a Customer count measure as distinct count in ssas and it respects all dimensions like geography, date, products etc. This measure actually gives us the number of customers purchasing across dimensions/hierarchies. Is it possible to have a measure which will give us non-purchasing customer count which will also respect all dimensions etc.? If yes, how to create such measure in ssas?
How do you define a ‘non purchasing customer’?
dear chris,
how are you?
i hope fine!
do you ever tried doing this in tsql?
regards
Pedro
No, I’ve never tried this on TSQL, sorry
Hi Chris,
This works great for me – I’m doing a small variation for “Customers Sold Cumulative” so that by month you can see customers sold (distinct count measure) and customers sold cumulative (MDX below):
CREATE MEMBER CurrentCube.MEASURES.[Customers Sold Cumulative] AS
COUNT(
NONEMPTY(
{[Customers].[Customer ID].[Customer ID].MEMBERS}
, {[Measures].[Dollar Sales]}
* {NULL : [Dates].[Calendar].CURRENTMEMBER}
))
Only problem is that this calculation does not “slice” by customer attributes. If you put, say, Customer Type on rows on columns, the Cumulative calculated measure repeats the same number. Same behavior on filters – customer-related filters do not change the behavior.
Can you please offer some help?
You probably need to add an EXISTING, like so:
CREATE MEMBER CurrentCube.MEASURES.[Customers Sold Cumulative] AS
COUNT(
EXISTING
NONEMPTY(
{[Customers].[Customer ID].[Customer ID].MEMBERS}
, {[Measures].[Dollar Sales]}
* {NULL : [Dates].[Calendar].CURRENTMEMBER}
))
Thanks Chris, worked perfectly. You’re the best!!
hi Chris, I am new to MDX and trying to get customer sales for given date range (for example 2001.07.24 – 2007-08-01) for specific sales type.New customer means they have no transaction for previous two months.İ wrote something but didnt work.My query is below,
SELECT
{[Measures].[Fact Islem Count],[Measures].[Amount]} on columns
,
EXCEPT(
NONEMPTY
(
[Customer].[CustomerNumber].[CustomerNumber].ALLMEMBERS ,
([Measures].[Fact Islem Count],[Date].[Dt].&[2001-07-24T00:00:00] : [Date].[Dt].&[2008-08-01T00:00:00])
)
,
NONEMPTY
(
[Customer].[CustomerNumber].[CustomerNumber].ALLMEMBERS ,
OpeningPeriod ([Date].[Hierarchy].[Dt], [Date].[Hierarchy].[Dt].&[2007-07-24T00:00:00].Parent.Lag(2)) :
ClosingPeriod ([Date].[Hierarchy].[Dt], [Date].[Hierarchy].[Dt].&[2007-07-24T00:00:00].Parent.Lag(1))
)
)
ON rows
FROM [cubeSales]
where ([Tran].[TranType].&[C])
Hi Chris
I’m trying to write a query similar to this, but rather than looking at how many customers in a time period were returning/new, I’d like to look at how many customers in a time period became a returning customer (i.e. of all customers in January, how many made a further purchase within 12 months).
I’d also like to return my results as a list of customer IDs along with details about their subsequent sales (e.g. product category, sale date).
Any advice>
Hi Chris
I’m trying to write a similar query to this, but rather than looking at the number of customers in a given period who are new/returning, I want to look at the number of customers in a given period who subsequently returned (i.e. customers in January 2016 who made a further purchase in 2016).
I would also like my results to be returned as a list of customer IDs along with details of the sale e.g. product category, sale date.
Any advice?
The basic approach of using NonEmpty() and then Intersect() would be the same, but if you want to see the customer IDs then you’ll need to put that set on the rows axis of the query rather than doing all the work in a calculated measure.