Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following table:
Storage facility | Material ID | Material needs last year | Material needs this year | Estimated needs ( =Max(this year, last year) |
---|---|---|---|---|
1 | xx | 100 | 120 | 120 |
1 | xy | 80 | 0 | 80 |
1 | xz | 50 | 70 | 70 |
2 | xx | 80 | 0 | 80 |
2 | xy | 50 | 30 | 50 |
2 | xz | 40 | 20 | 40 |
Total needs by material ID:
xx - 200
xy - 130
xz - 110
I want to create a new table looking at the aggregate needs only
TABLE 2
Material ID | Estimated needs ( =Max(this year, last year) |
---|---|
xx | 200 |
xy | 130 |
xz | 110 |
I am using this function:
=sum(aggr(Max(Rangemax(Material needs this year,Material needs last year)), Material ID, Storage Facility))
My problem is that the function yields these values as max value:
torage facility | Material ID | Material needs last year | Material needs this year | Estimated needs ( =Max(this year, last year) |
---|---|---|---|---|
1 | xx | 100 | 120 | 120 |
1 | xy | 80 | 0 | 0 |
1 | xz | 50 | 70 | 70 |
2 | xx | 80 | 0 | 0 |
2 | xy | 50 | 30 | 50 |
2 | xz | 40 | 20 | 40 |
Thus the aggregation does not result in the desired outcome.
I have tried to use an IF formula instead, but the problem is related to the AGGR part of the function.
Any ideas?
Which column are you using the expression on??? Can you post a sample qvw file to see the issue you are facing?
See the attached file.
You could just use a straight Table,
Material ID as Dimension, and sum(if(thisYear > lastYear, thisYear, lasYear)) as expression.
another approach would be the expression you mentioned extended with a total for the Mat ID:
=sum(TOTAL <Material ID> aggr(Max(Rangemax(Material needs this year,Material needs last year)), Material ID, Storage Facility))
Patrick
sum(aggr(RangeMax([Material needs last year],[Material needs this year]), [Storage facility], [Material ID]))
Hi Sunny T,
I apologize that my description of the issue lacks details. I had to put it together fast before I went home from work.
Here are some more details:
Sample of source data:
Storage facility | Material ID | Month | Material needs last year | Material needs this year |
---|---|---|---|---|
1 | xx | 1 | 120 | - |
1 | xy | 1 | 80 | 0 |
1 | xz | 1 | 50 | 100 |
1 | xx | 2 | 30 | - |
1 | xy | 2 | 40 | 50 |
1 | xz | 2 | 60 | 30 |
2 | xx | 1 | 80 | - |
2 | xy | 1 | 50 | 70 |
2 | xz | 1 | 40 | 20 |
2 | xx | 2 | 50 | - |
2 | xy | 2 | 40 | 30 |
2 | xz | 2 | 60 | 20 |
One of my tables should show me the the following:
Thus I choose to look at month 1 and 2 combined
Storage facility | Material ID | Estimated material needs ( =Max(this year, last year) |
---|---|---|
1 | xx | 150 (from material needs last year) |
1 | xy | 120 (from material needs last year) |
1 | xz | 130 (from material needs this year) |
2 | xx | 130 (from material needs last year) |
2 | xy | 100 (from material needs this year) |
2 | xz | 100 (from material needs last year) |
To calculate the estimated needs, it seems to work when I calculate the sums of last and this year given the selection of month 1 and 2, and use an IF function to pick the largest amount.
The Twist:
In my second table I want to drop the Storage facility dimension. and I want the sum of estimated needs by material ID to yield the same total as if i summarized by material ID in the table above:
Desired result:
Material ID | Estimated needs ( =Max(this year, last year) |
---|---|
xx | 280 |
xy | 220 |
xz | 230 |
I tried to use this function:
=aggr(Max(Rangemax(Material needs this year,Material needs last year)), Material ID, Storage Facility))
However, this did not get me there so I checked what the aggr-function actually calculate. This is what I got when I included all the dimensions AND added the aggr-function as an expression:
Storage facility | Material ID | Estimated needs when using the IF formula | Estimated needs (aggr-function) |
---|---|---|---|
1 | xx | 150 (from material needs last year) | - |
1 | xy | 120 (from material needs last year) | 120 |
1 | xz | 130 (from material needs this year) | 130 |
2 | xx | 130 (from material needs last year) | - |
2 | xy | 100 (from material needs this year) | 100 |
2 | xz | 100 (from material needs last year) | 100 |
The Challenge:
It seems that when I have blank cells for needs this year, the aggr function does not work properly.
Any ideas?
You just need to aggregate the materials need across all months per your aggr() dimensions:
=Sum(
aggr(
Rangemax(Sum([Material needs this year]),Sum([Material needs last year])),
[Material ID],[Storage facility])
)
Hi,
Thanks for the reply.
I have tried the function but it does not seem to work properly.
Thus, I did some research on where the error might lie.
It seems the aggregation function does not interpret my expression correctly. Look at the picture and see that I have tried to replicate a column using an Aggr-function. 'I did this only to investigate what actually came out of the aggr-function, thus I included all dimensions I aggregated on.
It yields the correct result only in some occations.
I thought the Aggr - function could be used to create a "hidden" table and that i should get the same results?
My expression has not worked in the sample I've attached? Could you detail where it failed?
Or if it hasn't worked in your real scenario, could you update the sample data to better match your scenario?
It's quite hard to tell what happens only looking at screenshots.