Count where NOT in a dimension but is in the previous dimension

[#1]

Hi All,

I have attached an example data set.  What I want to do is for each week, count distinct customers who were NOT present in the week of the dimension but WERE in the previous dimension.

I am hoping to do this using set analysis with the use of no aggr or if statements as these are computationally restrictive for large data sets.

Does anyone have any good ways to do this?  The problem obviously is how to count people who by definition are not present in that week. 

I can't use the ABOVE or BELOW functions because I want to count distinct people based on Customer number.

Cheers!

GPC

[#2]

Hi,

Seems it will be hard to do with set analysis. Counting all values normally limits you to the dimension, didn't find so far a proper way to overcome that.

I found 2 other ways, see attached: one with the concat idea (but can only work if no distinct customer reference have characters in common), another by building an aggregate table during load.

Hope it helps.

[jerem1234]

I don't think this would be possible in set analysis, since set analysis works on the entire set of values, not for individual rows. So one set you wish to have for say 'Jan 2013' would be just 'Feb 2013' but you dont wan't this set for 'Mar 2013' but instead wish to have only the set 'Feb 2013'. Two different sets of values. If you wish to avoid the record functions like above and below, then you'll have to use some other functions and with this I don't see how you'll avoid using the aggr function. I created a formula that does what you want using aggr, concat, subfield, and rank as follows:

subfield(concat(total aggr(concat(distinct Customer,','),Month_D),'|', Month_D),'|',rank(-Month_D)-1)

OR if you wish to do the count:

subfield(concat(total aggr(count(distinct Customer),Month_D),'|', Month_D),'|',rank(-Month_D)-1)

Maybe you could use a little deception and make the appearance of the previous month by changing the dimension to something like:

Monthname(addmonths(Month_D,1))

And if you want, could move this into a loadscript and create a new field like PreviousMonth.

PFA

Hope this helps!

[Christian_Lauritzen]

Very nice solutions. I see that Pierre's solution does the job in the third expression of the table "week customers". Gareth should be happy. Clever!

However, I cannot let go of finding a set analysis solution. With only one state, I agree that it is impossible. But I trigger on impossible. But what if we introduce a second state? I made two states: "Current" as the main state and "Prev" as the state to access the previous week.  Then I wrote the following expression:

=Concat(DISTINCT Total <WeekNum> {<Customer=P({Prev <WeekNum={$(=Current::WeekNum-1)}>} Customer)-P({Current} Customer)>} Customer,' ')

Still does not work though. Any clever elaborations on that one? Is it even possible?