topic Aggr Above in QlikView

Aggr Above

Mon, 11 Jan 2016 09:21:09 GMT

Hi,

my aggregation is not working. The table is

_Date, ID, Event

4.2.2015, 71, O

4.2.2015, 6, O

4.2.2015, 31, O

1.1.2015, 3, I

3.1.2015, 5, I

3.1.2015, 61, I

3.1.2015, 7, I

and the expression

aggr(RangeSum(Above( count({$< Event = {I}>} ID) - count({$< Event = {O}>} ID), 0, rowno())), Land, Month_Year)!

The wrong Result in the table you can see in the attacted file. The correct result would by

Jan 2015 D = 2

UK = 1

A = 1

Feb 2015 D = 1

A = 0

UK = 0

I think the problem is the sorting of the values in the INLINE table?! But how I can handle this problem?

Thank you

Re: Aggr Above

Digvijay_Singh — Mon, 11 Jan 2016 09:30:53 GMT

having personal edition so cannot open qvw but I think Event values in set expression needs to be in single quote, It appears 'I' and 'O' are text values so may need single quote. Not sure if more things involved.

Re: Aggr Above

Mon, 11 Jan 2016 10:14:43 GMT

OK update...

In the attachted fiel you can see the table with the expressions IN and OUT. About this 2 expressions you want to make a accumulation. The Goal is to have a table or a diagramm with dimension Month_Year and a accumulation about the several lands... Rigth beside the table you can see the right result !

Re: Aggr Above

sunny_talwar — Mon, 11 Jan 2016 11:11:27 GMT

Next Script:

Fact:

LOAD * INLINE [

_Date, ID1, Event

4.2.2015, 71, O

4.2.2015, 6, O

4.2.2015, 31, O

1.1.2015, 3, I

3.1.2015, 5, I

3.1.2015, 61, I

3.1.2015, 7, I

];

Land:

LOAD * INLINE [

ID1, Land

3, D

31, D

5, D

6, A

61, A

7, UK

71, UK

];

Join(Fact)

LOAD *

Resident Land;

FinalFact:

LOAD _Date,

ID1 as ID,

Event

Resident Fact

Order By Land, _Date;

FinalLand:

LOAD ID1 as ID,

Land

Resident Land;

DROP Tables Fact, Land;

Tmp_Fact:

NoConcatenate

LOAD *

Resident FinalFact;

Calender:

NoConcatenate

LOAD _Date

, _Date as Date

, month(_Date) as Month

, year(_Date) as Jahr

, date(MonthStart(_Date), 'MMM-YYYY') as Month_Year

Resident FinalFact;

New Expression:

Aggr(RangeSum(Above(Count({$<Event = {I}>} ID), 0, RowNo()), -Above(Count({$<Event = {O}>} ID), 0, RowNo())), Land, Month_Year)

Re: Aggr Above

swuehl — Mon, 11 Jan 2016 11:41:10 GMT

I believe you just need to order your Tmp_Fact table, create your calendar from that and drop the Tmp_Fact table:

Fact:

LOAD * INLINE [

_Date, ID, Event

4.2.2015, 71, O

4.2.2015, 6, O

4.2.2015, 31, O

1.1.2015, 3, I

3.1.2015, 5, I

3.1.2015, 61, I

3.1.2015, 7, I

];

Tmp_Fact:

NoConcatenate

LOAd

Resident Fact

Order by ID, _Date;

Calender:

NoConcatenate

LOAD

_Date

, _Date as Date

, month(_Date) as Month

, year(_Date) as Jahr

, date(MonthStart(_Date), 'MMM-YYYY') as Month_Year

Resident Tmp_Fact;

DROP TABLE Tmp_Fact;

Land:

LOAD * INLINE [

ID, Land

3, D

31, D

5, D

6, A

61, A

7, UK

71, UK

];

Re: Aggr Above

swuehl — Mon, 11 Jan 2016 11:43:30 GMT

Aggr() will sort its dimension values by load order, so you need to sort your calendar fields chronological when loading in, to make above() work as expected in the aggr() expression.

Re: Aggr Above

sunny_talwar — Mon, 11 Jan 2016 11:45:26 GMT

Stefan -

I think we might have got lucky that ID and Land will be sorted same way using the current data, but what if this was the Land Table?

Land:

LOAD * INLINE [

ID, Land

3, D

31, A

5, D

6, D

61, A

7, UK

71, UK

];

I don't think that Land would sort correctly in that case.

Re: Aggr Above

swuehl — Mon, 11 Jan 2016 12:06:55 GMT

Land sorted correctly with respect to what? I believe it doesn't matter for the aggr() above() expression, since it will be reset at column segment borders anyway.