
Re: Aggr function with rangemax
Sunny Talwar Oct 23, 2015 10:49 AM (in response to Knut Erik Thorsen)Which column are you using the expression on??? Can you post a sample qvw file to see the issue you are facing?

Re: Aggr function with rangemax
Knut Erik Thorsen Oct 25, 2015 4:31 AM (in response to Sunny Talwar )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.
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 aggrfunction actually calculate. This is what I got when I included all the dimensions AND added the aggrfunction as an expression:
Storage facility Material ID Estimated needs when using the IF formula Estimated needs (aggrfunction) 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.
 My hypotheses is that I need a way to make the Aggrfunction reed the blanks as 0 values.
 This does not seem to be soleved by adding an IFfunction saying that if the value is blank then use 0
Any ideas?

Re: Aggr function with rangemax
Stefan Wühl Oct 25, 2015 9:55 AM (in response to Knut Erik Thorsen)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])
)

comm188897.qvw 151.5 K

Re: Aggr function with rangemax
Knut Erik Thorsen Oct 26, 2015 8:25 AM (in response to Stefan Wühl )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 Aggrfunction. 'I did this only to investigate what actually came out of the aggrfunction, 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?

Re: Aggr function with rangemax
Stefan Wühl Oct 26, 2015 12:38 PM (in response to Knut Erik Thorsen)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.




Re: Aggr function with rangemax
Miguel Braga Oct 23, 2015 11:21 AM (in response to Knut Erik Thorsen)See the attached file.

Re: Aggr function with rangemax
Patrick Roser Oct 23, 2015 11:38 AM (in response to Knut Erik Thorsen)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

Re: Aggr function with rangemax
Massimo Grossi Oct 23, 2015 12:40 PM (in response to Knut Erik Thorsen)