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:
- Assume there are more months than month 1 and 2, but this is a sample
- In my dashboard I choose what month to look at
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:
- Dim: Storeage facility, Material ID
- Fact: Estimated material needs (Max of material needs this year vs last year)
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.
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:
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
It seems that when I have blank cells for needs this year, the aggr function does not work properly.
- My hypotheses is that I need a way to make the Aggr-function reed the blanks as 0 values.
- This does not seem to be soleved by adding an IF-function saying that if the value is blank then use 0
You just need to aggregate the materials need across all months per your aggr() dimensions:
Rangemax(Sum([Material needs this year]),Sum([Material needs last year])),
[Material ID],[Storage facility])
comm188897.qvw 151.5 K
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?
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))