topic Re: Aggr function with rangemax in QlikView

Aggr function with rangemax

Fri, 23 Oct 2015 14:19:46 GMT

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?

Re: Aggr function with rangemax

sunny_talwar — Fri, 23 Oct 2015 14:49:43 GMT

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

miguelbraga — Fri, 23 Oct 2015 15:21:00 GMT

See the attached file.

Re: Aggr function with rangemax

patroser — Fri, 23 Oct 2015 15:38:32 GMT

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

maxgro — Fri, 23 Oct 2015 16:40:24 GMT

sum(aggr(RangeMax([Material needs last year],[Material needs this year]), [Storage facility], [Material ID]))

Re: Aggr function with rangemax

Sun, 25 Oct 2015 08:31:09 GMT

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 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.

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

Any ideas?

Re: Aggr function with rangemax

swuehl — Sun, 25 Oct 2015 13:55:02 GMT

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])

)

Re: Aggr function with rangemax

Mon, 26 Oct 2015 12:25:08 GMT

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?

Re: Aggr function with rangemax

swuehl — Mon, 26 Oct 2015 16:38:00 GMT

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.

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

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

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