Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Aggr Above

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

7 Replies
Digvijay_Singh
Honored Contributor III

Re: Aggr Above

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.

Not applicable

Re: Aggr Above

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

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)

Capture.PNG

MVP
MVP

Re: Aggr Above

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

];

MVP
MVP

Re: Aggr Above

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

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.

MVP
MVP

Re: Aggr Above

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.

Community Browser