
Calculating a set aggregation for each row of data
bglbi Jul 8, 2010 9:18 AM (in response to amosmoss)This certainly can be done in QlikView. It requires the use of set analysis to calculate the mean (denominator) independent of selections:
Dimensions:
Product, Sales Date
Expression:
Sum(Sales) / Sum( {1} Sales)
By using the {1} you are calculating the sum independent of the product selected.
/emb

Calculating a set aggregation for each row of data
amosmoss Jul 8, 2010 10:10 AM (in response to bglbi)Thanks for the quick response, bglbi.
I tried that in a straight table chart, and what I got is a straight 1 for every date and product. It didn't matter whether I selected something or not.
This expression does not include any reference to the fact that I want the sum to be over all of the days for each product, and not over the products for each day (althoug that makes sense too). From what I understand of the documentation, sum({1} sales) will be the same for every day and product in the dataset, while I need the sum of sales over all the days, of each product, on each row.
I hope the next table makes what I need clear:
Day Product TotalProductSales Day'sSales Day'sRelativeSales
1/4/2010 ProductA 1000 300 30%
2/4/2010 ProductA 1000 100 10%
3/4/2010 ProductA 1000 600 60%
1/4/2010 ProductB 200 90 45%
2/4/2010 ProductB 200 90 45%
3/4/2010 ProductB 200 10 5%
4/4/2010 ProductB 200 10 5%
Any ideas?
Amos.

Calculating a set aggregation for each row of data
John Witherspoon Jul 8, 2010 7:09 PM (in response to amosmoss)I believe you want this?
TotalProductSales = sum(total <Product> Sales)
Day'sSales = sum(Sales)
Day'sRelativeSales = "Day'sSales"/"TotalProductSales"
Calculating a set aggregation for each row of data
amosmoss Jul 11, 2010 4:13 AM (in response to John Witherspoon )Thank you very much, John. That seemed to be just what I needed.
Now, just to understand more, I have a few questions:
 From what I understand, the "total" keyword indicates that the sum is taken over a different context than the current selection. But what's the <product> indicating? Does it mean that the context is all records with the same product as the current record? Did I get that right?
 I read something about the aggr function. Is sum(total <Product> Sales) the same as aggr(sum(sales), product)? If it is, than what's the difference between the two?
 When I played with the data, I noticed that if I set Day'sSales to be = Sales, there wasn't any data displayed for this column. Why is that? If I only have one record for each day and product, why sould I sum it?
 Can I display only rows that satisfy a given critrion? For example, I want to display only the exceptional days for each product (more than 20% of the total sales).
And once again, thanks for all the help. I think I'm gradually getting the intuition behind qlikview .
Amos.

Calculating a set aggregation for each row of data
John Witherspoon Jul 12, 2010 2:03 PM (in response to amosmoss)1. The "total" keyword indicates that the sum is to be taken across the entire table, not just a row on the table. So it changes the context from the row to the entire table. The <product> part says that no, we don't actually want the ENTIRE table, just all of the rows on the table with the same product as the current row.
2. You know, I hadn't really thought about it before, but they ARE very similar. Aggr() does seem to take the whole table as the context, so it's kind of like using the "total" keyword by default. And specifying aggr(...,product) will also make it so that you're only looking at the matching product. It's a little funny, though, in that the aggr() will only assign the resulting sum to ONE of your rows. Still, you can use the "nodistinct" keyword to make it repeat the values. Then the only difference would seem to be in totals, where the aggr() ends up returning multiple values, and so evaluates to null.
3. I'm not sure what you're saying.
4. You could probably handle it with a calculated dimension, maybe like this:
aggr(if(sum(Sales)/sum(total <Product> Sales)>0.2,Product),Product)

Calculating a set aggregation for each row of data
Matt Sweeney Jul 12, 2010 10:37 PM (in response to John Witherspoon )John,
Wouldn't you expect some performance issues from using a calculated dimension like the one you supplied?

Calculating a set aggregation for each row of data
John Witherspoon Jul 12, 2010 10:44 PM (in response to Matt Sweeney )Yes, you could easily have performance issues with a calculated dimension like that, at least if you have much data. The trouble is  what's our alternative? The only dynamic alternative I'm thinking of is applying a very similar condition to EVERY expression. It might perform better given how bad calculated dimensions can be, but it'd be a lot more trouble, and I wouldn't expect it to perform well either, just better.
Now, if we don't need our definition of "top 20%" to be sensitive to selections, if it could be static, then we could do something in the script, and then our performance would be just fine. So I suppose it depends on our definition of the top 20%. Do we ALWAYS mean for our ENTIRE data set? Or should it be sensitive to selections?
Or maybe someone knows a dynamic solution better than a calculated dimension or expression?


Calculating a set aggregation for each row of data
amosmoss Jul 14, 2010 4:18 AM (in response to John Witherspoon )Hi John,
3. My mistake, sorry :)
4. I'm sorry, but I didn't quite get that. Aren't the dimensions calculated before the expressions? If so, how could I create a calculated dimension based on an expression (this record's relative sales)?
Thanks,
Amos.

Calculating a set aggregation for each row of data
John Witherspoon Jul 14, 2010 2:55 PM (in response to amosmoss)The aggr() basically makes an internal chart by Product, from which it extracts the "over 20%" products as your dimensions for the real chart, all before calculating the expressions in the real chart. Assuming I don't have a syntax error or some other problem.

Calculating a set aggregation for each row of data
amosmoss Jul 20, 2010 7:46 AM (in response to John Witherspoon )John, thank you very much for all of the answers. It really helped a lot.
Bye.





