Aggr Above

[#1]

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

[Digvijay_Singh]

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.

[#3]

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 !

[sunny_talwar]

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)

[swuehl]

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

];

[swuehl]

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.

[sunny_talwar]

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.

[swuehl]

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.