Advanced nested set analysis

[#1]

Can anyone advise if this is actually possible? I'm certainly struggling.

I want to identify new customers who didnt take a certain type of product during the first half of the year, who are taking it now. That bit is easy enough, I did it something like this:

count({$<Customer=e({<Month={">=Jan <=Jun"}>})>} distinct Customer)

There were a few other qualifiers, but that was the gist of it. The problem then arises that my business wish to expand this criteria to include the customers captured in the above criteria, i.e. those who didnt buy anything in Jan - June, and also those who only took one specific type of product (we'll call it shoes) during the period Jan-June and then went on to buy a different product in the second half of the year. I've been trying to find a way to intersect the Jan to June selection with buying a specific product and its hurting my head.

I've tried the following kinds of expression: (lets pretend we only do 3 products: shoes, hats and scarves)

This one should identify this second batch of customers, who had only taken shoes during Jan - June:

count({$<Customer=p({<Month={">=Jan <=Jun"}, Product-={"hats", "scarves"}>})>} distinct Customer)

(of course, selecting Product = {"Shoes"} may return those who had also bought other products in the data range, which is not what I wanted)

The help text around p() and e() sets specifies only one evaluation between the brackets. The above expression does appear to work though, although it seems to work as an or rather than an intersection. The comma cant be replaced with a * or the expression becomes invalid. I've tried using an intersection between P() sets, but without the date range on the second set, it also excludes people who bought the other products outside of the date range, eg.

count({$<Customer=p({<Month={">=Jan <=Jun"}>})* p({<Product-={"hats", "scarves"}>})>} distinct Customer)

Does anyone have any ideas about how to approach this?

Thanks

Noel

[jerem1234]

Try something like:

count({$<Customer = p({<Month={">=Jan <=Jun"}>*<Product-={"hats", "scarves"}>})>}distinct Customer)

Hope this helps!

[#3]

Hi,

Thanks for the answer. It doesn’t appear to work when I try it in this example.

[jerem1234]

Can you provide the tables that you are looking to return? the output you are looking for?

[jaimeaguilar]

Hi,

the expression that Jeremiah Kurpat suggested works. However, you have to modify it a little bit, because in your example Month doesn't exist. Instead there's a field called Period, which is (i guess) a numeric field representing the months. If you want to test the expression in the example you attached here, modify the expression like this:

count({$<Customer = p({<Period={">=1 <=6"}>*<Product-={"hat", "scarves"}>})>}distinct Customer)

Alternatively, you can get to the same result without using P() and instead using advanced search:

count({$<Period={">=1 <=6"}>*<Product-={"hat", "scarves"}>}distinct Customer)

Also note that in your example Hat is in singular, so typing hats in set analysis will return you a wrong result (AAAA and CCCC)

Probably, in a final application, you'll need to use the month dimension with a properly created master calendar.

As a result, you'll see that only AAAA is part of this set because:

1. This Client bought between month 1 (Jan) and Month 6 (Jun)
2. AND although it also bought Hat and Scarves (which are being excluded from this set), this client ALSO bought Shoes, so this formula returns ONLY the ocurrences (Records) where AAAA bought Shoes. You can validate this by creating a straight table containing period, quantity and/or product and using the suggested expression.

So, in conclusion, the expression suggested here returns those clients who bought between Jan (Period 1) and June (period 6) and that didn't bought hat OR scarves. In general, your requirement can be solved implementing set theory within set analysis,

regards

[#6]

I could well be missing something but I don’t believe the suggested expression works. I have updated the document I posted yesterday to show some more information about what I am trying to achieve, which is:

“All customers who are brand new in P7 onwards, plus customers from P7 onwards who had only every taken shoes in P1 – P6”

The expression suggested above seems to present an intersection in customer of those in one group that have traded in P1 - P6 and those in a second group that have only ever bought shoes. This doesnt give the same group as those who, between P1 - P6,  only bought shoes.

[jerem1234 · Accepted solution]

Try this expression:

sum({<Customer = p({<Period = {'>=1<=6'}, Product = {'Shoes'}>})-p({<Period = {'>=1<=6'}, Product = {'Hat', 'Scarves'}>}),Period={">6"}>} Qty)

This pretty much says "All customers who bought shoes in periods 1-6 minus any customer who also bought hats or scarves in the period of 1-6"

Hope this helps!

[#8]

Spot on. Many thanks, that works perfectly!