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.

[johnw]

trent.jones wrote:In a perfect world, I would like to simply put the dimension as [Product Name] and have 1 expression that just counts them all

What's "them all"? The number of customers for that product? The number of contracts for that product? Something else? Let's start by trying to give you what you want, not what you think you have to settle for.

[#3]

Let's say we have 1000 customers and 5 product types. I want to know how many customers (the count) are on type 1, type 2, type 3, etc. Most customers have old contracts of different types, so I've been trying to use the max of contract ID (the newest one) to indicate that this is the active contract.

Ideally it would look like this when finished:

Product: Count

Type 1 45015

Type 2 22102

etc.

Thanks for the speedy reply.

[#4]

On a related note, can you explain this from the QV Reference Manual? These are direct quotes:

From page 1330:

"The dimension arguments of the aggr function may of course contain calculated dimensions, which in turn make use of the
aggr function."

Page 1222: Definition of aggr

"Each dimension must be a single field. It may not be an expression (calculated dimension)."

To me it looks like the first one says "the dimensions inside of aggr can be calculated dimensions" and the second one says "the dimensions inside of aggr may not be calculated dimensions". Am I missing something here?

[johnw]

If I've understood, then attached is one approach. I create a separate table in the script that flags the active contract for each customer. You can then use that flag to narrow down your data. One way is to add it to the chart as a hidden dimension and suppress nulls. Another way is to use set analysis. The example shows both of these ways. I suspect that the hidden dimension approach will render faster for very large data sets, but that's only a guess.

[johnw]

trent.jones wrote:To me it looks like the first one says "the dimensions inside of aggr can be calculated dimensions" and the second one says "the dimensions inside of aggr may not be calculated dimensions". Am I missing something here?

I was only able to find the second quote in whatever version of the manual I have, but those do look inconsistent to me. I've never tried it before, but a little testing seems to indicate that you CANNOT use calculated dimensions as dimensions in the aggr(). I'm on version 9 SR6 if that makes a difference.

[#7]

Since I cannot access the script, I had to do the flagging with:

=aggr(max([Contract]),[Customer])

and expression =-1 which did give me what I want

The problem now is that i do not know how to use Active? in an expression.

I have this =count({$<[Active?]={'-1'}>} distinct [Customer]) but if a customer has had 4 different products, it counts them all. I had to put the brackets in [Active?] because that was the only way that it would let me use it... It says "Bad Field Name".

Is there any way to do that without using the script editor?

[johnw]

Not quite sure what you're saying, but if you can't access the script, you can't create the [Active?] field, so you can't use that in the solution. So back to the drawing board.

OK, one way is this:

count({<Contract={$(=concat(aggr(max(Contract),Customer),','))}>} distinct Customer)

But I suspect it will fall apart with more than a few hundred customers, as it's creating an expression that explicitly lists ALL active contracts.

Hmmm.

[#9]

It's trying to do it right now, there are something like 350K+ customers and over 1million contracts between them so let's hope it finishes. I am 99% sure I tried the exact same thing except I didn't use concat, distinct, and I always use quotes when I have equations inside set analysis because it never seems to work when I don't. Could that have been my problem (assuming this works, which it looked like it did for the 1 customer that QV starts off selected when I boot it up)? I've looked several times and I don't really understand what the quotes actually mean inside set analysis, something about searches?

Thanks

[johnw]

Yeah, I really don't think it's going to work for 350k customers.

In set analysis, you should put literals in single quotes, and search expressions in double quotes. That's my rule, anyway. Set analysis syntax is a little loose here, leading to certain things that it should be able to do but can't due to the loose syntax, though I've never seen someone with a practical problem as a result, only me thinking up theoretical ones. I skipped the single quotes in my example so that I didn't have to type or explain all the chr(39)s that would have been necessary. You can get away with it on numeric literals; as long as the literal doesn't have white space in it, you don't need the single quotes.

Searches are things like ">=500" and the like. You're not looking for a literal value '>=500', but instead searching for all values greater than or equal to 500.