Calculated Dimension with a Messy Aggr()

Thu, 12 Aug 2010 15:47:12 GMT

I am trying to use an Aggr() and Calculated Dimension to group users based on different ranges. The expression used to count the metrics is pretty big. I am able to get the calculated dimension and everything working except I can't force QlikView to show all dimensions when there are no matches. In this example, I have Customers and each Customer has various FKs. I'm trying to give FKs points and then count the number of Customers that fit into different ranges.

This is an example of what I'm using for the Calculated Dimension:

=If(Aggr($(=vSA), Customer) >= 30, '30',
If(Aggr($(=vSA), Customer) >= 20, '20',
If(Aggr($(=vSA), Customer) >= 10, '10',
If(Aggr($(=vSA), Customer) >= 0, '0'))))

Then I use a simple expression:

Count(distinct Customer)

That setup works fine, except that when no customers fit into a certain range, it doesn't appear. I've tried Show All Dimensions and even making a dummy expression that should show for all. The dollar sign expansion uses a variable with a complicated Count. For the example, I used something like:

Count(distinct FK) * 5

On another topic, I saw some suggestions to create the dimensions in the load, so I did that, but I can't get anything to work in that situation. I've tried a few different things, but I can't seem to use the range dimension to count the Customers I want. I've attached an example that illustrates my problem, but the real application is significantly more complicated. I ran into the same issues when creating this sample as I have in my real app, so if I can solve it in the sample, it should apply to the full version. I can't calculate any of these values in the load, because the user needs to select a date range that would change the counts. Any ideas?

Calculated Dimension with a Messy Aggr()

Thu, 12 Aug 2010 16:20:59 GMT

As can be the case with posting new topics, I think I figured it out shortly after posting.

Something like this:

If(StartRange = 30, count(distinct total if(Aggr($(=vSA), Customer) >= 30, Customer)),
If(StartRange = 20, count(distinct total if(Aggr($(=vSA), Customer) >= 20
 and Aggr($(=vSA), Customer) < 30, Customer)),
If(StartRange = 10, count(distinct total if(Aggr($(=vSA), Customer) >= 10
 and Aggr($(=vSA), Customer) < 20, Customer)),
If(StartRange = 0, count(distinct total if(Aggr($(=vSA), Customer) >= 0
 and Aggr($(=vSA), Customer) < 10, Customer))))))

topic Calculated Dimension with a Messy Aggr() in QlikView

Calculated Dimension with a Messy Aggr()

Calculated Dimension with a Messy Aggr()