Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a pivot table as below which is only showing a small extract of the data. The problem I have is that unless I really filter in on the data the total for Value Added is not displayed.
product | fin_year | Value GBP | Value Added |
---|---|---|---|
MBEU179090 | 9934 | 7328 | |
MBEU179189 | 8753 | 7861 | |
MBEU179444 | 4224 | 3401 | |
MBEU179445-1 | 2390 | 1991 | |
MBEU179602-1 | 3599 | 3061 | |
MBEU179672 | 3223 | 2606 | |
MBEU185143 | 1570 | 1414 | |
MBEU200440 | 18351 | 14652 | |
MBEU200543 | 6716 | 5134 | |
MBEU205111 | 1149 | 1149 | |
MBEU205118 | 4460 | 4460 | |
MBEU215103 | 1098 | 1098 | |
Total | 65466 |
If I select each product in turn then the total is displayed
product | fin_year | Value GBP | Value Added |
---|---|---|---|
MBEU179672 | 3223 | 2606 | |
Total | 3223 | 2606 |
Any ideas what is causing this, the Value GBP field is setup in the same was but always returns a value
Hi!
You should use the function aggr.
Regards.
I suspect you might have a part of your expression that can't be uniquely calculated at a total level, in which case you should either enclose all of your components within your aggregation function, or use aggr() to calculate the expression at a lower level of detail and then aggregate those pre-calculated results.
If you show your expression, it would be easier to pin-point the problem
My expression as it stands is
Sum(order_book_value)-(standard_material*sum(sales_order_qty))
Exactly what I suspected... your field standard_material may return multiple possible values at the total level, and that renders the whole expression as a null. I'd recommend 2 changes:
1. Include standard_material within the sum()
2. Use function RANGESUM, to avoid getting null() when one of the two sum() returns a null() or missing.
The end result should look like this:
RANGESUM( Sum(order_book_value), -1 * sum(standard_material*sales_order_qty))
I believe this should work.