Aggr vs. Set Analysis vs. If Statements?

[#1]

I need to get a list of all of the product types that customers are currently on. I have Customer ID, Contract ID, and Product Name fields. Each customer ID and Contract ID is only on 1 product, and the max contract is always the active contract. I have tried literally hundreds of combinations of ifs,aggr's,and set analysis to no avail. One thing it loves doing is just spitting out what seems like a random product name instead of the name associated with the max contract ID. I tried setting the dimension to something like aggr(max[Contract ID],[Customer ID]) and an expression as [Product Name] and I really don't understand why this doesn't work. I would really appreciate an explanation as to why that isn't working.

In a perfect world, I would like to simply put the dimension as [Product Name] and have 1 expression that just counts them all, but that never works. I wouldn't be opposed to having the dimension as customer ID and then having an expression say something like "if the contract ID is the max for that customer, show the product type for that contract". I think the problem with that method is that the first part of the if works, but the second part doesn't specify to get the corresponding product name for that ID. I just graduated college and my degree is not in anything programming related so I'm not too sure...

I can't change anything in the script (I don't have access, clearly because I don't know what I'm doing) so I would appreciate a solution that just involves chart expressions.

Thanks.

[#2]

Wow thanks a lot this actually works. I spent so much time trying to figure this out (and I was so close!). I ran this for a smaller set of customers (3500) and it worked like a charm. I let it run for an hour on all 350K+ customers and it didn't finish by the time I got off work. What are the odds it will finish if I just let it run? It took like 10 seconds to do 3500 customers so I'm not sure why it can't do 350K (I do set analysis on 350K customers all the time and it usually goes fast, but I use quotes in those searches).

Thanks again.

[johnw]

When you say you do set analysis on 350K customers all the time, do you mean something like this?

sum({<Customer={'Customer 0000001','Customer 0000002',..every customer...,'Customer 350000'}>} Sales)

Or just that you have 350K customers, and you use set analysis? Because the approach I took is literally inserting the entire list of active contracts into the set analysis, then evaluating the resulting expression. I don't believe there's an official limit on expression size, but for 350K active contracts, that expression will be VERY long. And I know I've crashed out a couple of different versions of QlikView when trying to test extremely long expressions, just to see what would happen. So I'd be surprised if it finished. And in any case, something that takes all night to calculate certainly isn't going to satisfy your need, even if it technically works.

[#4]

I think I see the difference now, because what I did before didn't have aggr inside the set analysis.

One last question, what is the significance of the concat? I tried to take it out, but it didn't work unless it was there.

[johnw]

The concat() is both why it works, and why it doesn't work.

The concat() creates a list of all of the active contracts, separated by commas. Then the $() tells QlikView to insert this list directly into the expression. So it works because it's just like we'd listed every active contract manually. But it kills performance because it's just like we'd listed every active contract manually.

I think what we'd LIKE to do is come up with that set of contracts without needing to actually list them out explicitly like that. My current thought is that we might be able to use set analysis with P() notation in some way, but I haven't thought it through.

[#6]

What about this:

Dimension:

=aggr
(
if
(
[Contract Start Date]=max(total <[Customer Number]> [Contract Start Date]),
[Product Name]
)
,[Customer Number],[Contract ID]
)

Expression: =count(distinct [Customer Number])

A couple concerns that I have are:

1) Can I use the <[Customer Number]> inside the max( like that? I think in the reference manual it says that you must use total for inner aggregation (which I'm assuming this is) and it ignores all dimensions except what is inside the <>. Since this IS the dimension, does this still work?

2) Did I put the right dimensions ([Customer Number] and [Contract ID]) in the aggr? Am I even allowed to do this at all?

I did this same thing except changed [Product Name] to [Customer Number] and I just put the expression as [Product Name] and it looked like it worked for every customer that I checked... I just have a hard time believing that this actually works since I pretty much wrote this on my own (I must admit that I was inspired by a post that I saw) and I'm not sure I'm smart enough to come up with this...

Really appreciate it.