Problem with Aggr and maxstring/minstring functions

[#1]

I am attaching a small sample showing the concept of what I am trying to accomplish.  Any help would be appreciated.

I am trying to count Sales - there could be a blue sale or a pink sale.   There could also be a Combo sale - both pink and blue.  I am trying to use the maxstring and minstring of the type of sale - if the min and max are blue, then it's a blue sale.  If min and max are pink, then it's a pink sale.  If min is blue and max is pink, then it's a combo sale. 

Because of the way the real data is set up, there could be blank records as well, which I have included in the data. 

I cannot get this to work 100% of the time.  Any thoughts???

Please see the Sales by Color - Not working chart.

Thanks!!!

[swuehl]

Hi,

I think I have a solution to your problem, please see attached file.

Instead of the aggr, min/maxstring and if Functions, I used indirect set analysis.

For example

count({<Customer = p({<Category={'Blue'}, Outcome={'Sale'}>} Customer)*e({<Category={'Pink'}, Outcome={'Sale'}>} Customer) >} DISTINCT Customer)

to count the blue customers.

Hope this helps,

Stefan

[swuehl]

me again,

I was just curious why your solution haven't worked the first place.

I think there might be two problem:

1) Blank records for Category

which are not NULL, so that Minstring will return those blank records instead of 'blue'.

To fix this you might change the Load expression into:

LOAD SalePerson, Customer, Outcome, if(Category='',Null(),Category) as Category INLINE

[....]

2)

the actual expression (here for combo):

Count(DISTINCT If([Outcome]='Sale' and not isnull(Category) and aggr(minstring([Category]), Customer) = 'Blue' and   aggr(maxstring([Category]), Customer) = 'Pink' ,Customer))

With bob, you get a match here, even you shouldn't. The two conditions for color checking are fulfilled, and you have one record with Outcome = Sale, so it's a combo? No, it is not, because you have to disregard the records which are not 'Sale' also for color checking.

Maybe you could rework this expression in a way that's correct, I assume you finally get something similar to a my set expression.

Hope this helps, too,

Stefan