topic Aggr vs. Set Analysis vs. If Statements? in QlikView

Aggr vs. Set Analysis vs. If Statements?

Mon, 01 Nov 2010 17:56:23 GMT

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.

Aggr vs. Set Analysis vs. If Statements?

johnw — Mon, 01 Nov 2010 19:10:29 GMT


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.

Aggr vs. Set Analysis vs. If Statements?

Mon, 01 Nov 2010 19:34:32 GMT

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.

Aggr vs. Set Analysis vs. If Statements?

Mon, 01 Nov 2010 19:58:39 GMT

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?

Aggr vs. Set Analysis vs. If Statements?

johnw — Mon, 01 Nov 2010 20:09:45 GMT

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.

Aggr vs. Set Analysis vs. If Statements?

johnw — Mon, 01 Nov 2010 20:24:12 GMT


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.

Aggr vs. Set Analysis vs. If Statements?

Mon, 01 Nov 2010 20:31:36 GMT

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?

Aggr vs. Set Analysis vs. If Statements?

johnw — Mon, 01 Nov 2010 20:52:02 GMT

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.

Aggr vs. Set Analysis vs. If Statements?

Mon, 01 Nov 2010 21:10:35 GMT

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

Aggr vs. Set Analysis vs. If Statements?

johnw — Mon, 01 Nov 2010 21:22:44 GMT

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.

Aggr vs. Set Analysis vs. If Statements?

Tue, 02 Nov 2010 14:29:21 GMT

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.

Aggr vs. Set Analysis vs. If Statements?

johnw — Tue, 02 Nov 2010 16:02:16 GMT

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.

Aggr vs. Set Analysis vs. If Statements?

Tue, 02 Nov 2010 16:25:56 GMT

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.

Aggr vs. Set Analysis vs. If Statements?

johnw — Tue, 02 Nov 2010 17:06:52 GMT

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.

Aggr vs. Set Analysis vs. If Statements?

Wed, 10 Nov 2010 18:11:22 GMT

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.