topic Re: Problem with Aggr and maxstring/minstring functions in QlikView

Problem with Aggr and maxstring/minstring functions

Mon, 25 Jul 2011 16:42:37 GMT

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!!!

Re: Problem with Aggr and maxstring/minstring functions

swuehl — Mon, 25 Jul 2011 17:19:58 GMT

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

Problem with Aggr and maxstring/minstring functions

swuehl — Mon, 25 Jul 2011 18:26:20 GMT

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

[....]

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