12 Replies Latest reply: Jan 30, 2014 12:55 PM by Christian Lauritzen

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

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

• ###### Re: Count where NOT in a dimension but is in the previous dimension

Its very simple add one more Visit_Date dimension to the table.

Hope this is what you looking for find the file and check it.

• ###### Re: Count where NOT in a dimension but is in the previous dimension

Sorry Sunil - I think you've misunderstood my question.

• ###### Re: Count where NOT in a dimension but is in the previous dimension

Try Like this,

count({<Year=, Month=, Customer= E({<YearMonth={"\$(=monthname(max(YearMonth)-1))"}>}), YearMonth={"\$(=monthname(max(YearMonth)))"} >}distinct Customer)

Thanks,

Selva

• ###### Re: Count where NOT in a dimension but is in the previous dimension

Hi Selva,

I have tried something along these lines for testing:

concat({<Customer= P({1<Month_D={"\$(=monthname(addmonths(max(Month_D), -1)))"}>} Customer )>} distinct Customer, ',')

I just want to check what customers are coming through using the concat rather than a count.  Makes it easier for proving the concept.

Here I am trying to have the customers who transacted in the previous month listed against the dimension of month.  The problem I'm having is that the max(Month_D) here goes to the 'overall max', so I'm getting Mar 2013 -1 = Feb 2013.  So I'm getting 'those customers from February who transacted in the month of the dimension'.

Any help would be greatly appreciated!

Cheers,

GPC

• ###### Re: Re: Count where NOT in a dimension but is in the previous dimension

I have attached another example here.  Same data but with that expression in there and what I want in the text box.

Cheers!

GPC

• ###### Re: Count where NOT in a dimension but is in the previous dimension

Just use the above function around your existing current month function.

=above(concat( distinct Customer, ','))

The reason that set analysis won't work for this is because you are currently using the month dimension that you are trying to sort with as a dimension within the graph.

• ###### Re: Re: Count where NOT in a dimension but is in the previous dimension

Thanks, unfortunately the Above function won't work with customers when I want to count distinct or to include/exclude based on their behaviour in the past/future.  I understand the problem with the dimension, but this was exactly what I was trying to overcome with Set Analysis.

Is it simply impossible?

G

• ###### Re: Count where NOT in a dimension but is in the previous dimension

Hi Gareth,

Unfortunately it is impossible to overcome with set analysis. If this is something that is important then you can always work around this by using a for loop in your script (and consolidate the last months customers into a new field), that way you can create a few artificial fields that will not be excluded by your use of the month as the dimension.

Cheers,

Tyler

• ###### Re: Count where NOT in a dimension but is in the previous dimension

Tricky one.. Should be possible, but I haven't got to it yet. Suggest to start by adding WeekNum as a numeric counter for Week_D, also using WeekNum as main dimension.

Suggested Customer Expression:

=Concat( Distinct {<Customer = - P(Total {<WeekNum={"\$(=WeekNum-1)" }>} Customer)>} Customer,'.')

Still, as it does not work, those of you claiming this is impossible might be right

Would love to see a set analysis solution to this one, preferably without making any alterations to the original data structure.

Christian

• ###### Re: Count where NOT in a dimension but is in the previous dimension

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.

• ###### Re: Count where NOT in a dimension but is in the previous dimension

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:

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

PFA

Hope this helps!

• ###### Re: Count where NOT in a dimension but is in the previous dimension

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?