I am trying to create an expression that will give the weighted average of a field at the total and any grouping layers. I am able to create a secondary field that displays the weighted avg, but the underlying rows do not return the true value, but a useless weighted value. See the data in the below table. I want to create a weighted aggregation of the 'Book_Yield' field. I am able to create the aggregate value with the expression:
However, at the underlying rows, the data is useless, i need to return the data i see in the Book_Yield field, but with the total row from the complex aggr() function.
I actually just figured it out...finally. So simple, but frustrating to get enough understanding of how qlikview calculates its aggregations that it took so long.
I've gotten a bit further, but an certainly stuck now. I have a function that will return the values that need to be summed, but i can not get these values to sum correctly.
BASE_BOOK_VALUE/
sum(total <ACCT_SYSTEM_PORTF_NO_HOLD,
PACE_SEC_TYP_LVL1_DESC,
PACE_SEC_TYP_LVL2_DESC,
PACE_SEC_TYP_LVL3_DESC> BASE_BOOK_VALUE)
*BOOK_YIELD
That being the expression, I can not seem to implement sum(aggr(your expression, your dimensions)). the above returns the correct value at the lowest level of the pivot table, but not at any of the partial sums. If i change the above to:
sum(BASE_BOOK_VALUE)/
sum(total <ACCT_SYSTEM_PORTF_NO_HOLD,
PACE_SEC_TYP_LVL1_DESC,
PACE_SEC_TYP_LVL2_DESC,
PACE_SEC_TYP_LVL3_DESC> BASE_BOOK_VALUE)
*sum(BOOK_YIELD)
Changing it to the above retuns the wrong number because at the partial_sum row, it calculates the sum of each 3 columns without summing the values from the lower level which were properly calculated.
The calculation you are working on seems to be : ( sum( bookvalue) / sum( subtotal bookvalue ) ) * Book yield
1) Is the subtotal value the grand total for the whole chart or is it always the subtotal ? In your example there are 3 subtotals and 1 grand total. Just trying to figure out what is my denonominator and does its value change for every level of the pivot chart ...
2) How do you calculate Book Yield at the subtotal level. I'm guessing its not just a sum...
Technically, the ideal calculation is actually: sum( ( bookvalue / sum( subtotal bookvalue ) ) * Book yield )
1) the subtotal value is essentially sum(bookvalue) of the next higher Dimension (in the case of the first dimension, it is of the entire data set). The denominator changes at each dimension. I was going to handle this by creating a pick() and use different calculations at each dimension, but I can't seem to get the first subtotal to work on dimension 3.
2) Book Yield at the lowest level (the raw data which corresponds to the security_key level) is a direct data point from the database. It is not calculated. I am merely trying to aggregate the value at each dimension subtotal/grandtotal.
Its ugly but I think I have #1 with this expression. I've added it to the chart with a new field called 'denom'. Please validate it is what you are after.
For #2 I'm not sure what to multiple the subtotaled fields by. Book yield values exist at the lowest level, but at the subtotal what is the aggregated book yield. If you can give me the business logic or a quick sample it would help. IE: if I have 3 detailed records each with a book yield of 1 , 2, and 3. Is the subtotaled book yield 6 (sum) , 2 (average) .... ?
Thx Johnny, #1 does return the correct denominators. If you see the d3_Total field in my qvw, you would see the same thing, though done using the sum(total <dim>) instead of the aggr() function in order to run faster on large data sets, which mine eventually will be. Please see a short example of weighted avg below:
Book_yield
Book Value
Total Row
=(1*100/600)+(2*300/600)+(3*200/600)=2.16666667
600
1
100
2
300
3
200
if you were to do the same calculation in Excel it would be:
I actually just figured it out...finally. So simple, but frustrating to get enough understanding of how qlikview calculates its aggregations that it took so long.
.png "Former Employee"){kind=icon}