7 Replies Latest reply: Jan 11, 2016 7:06 AM by Stefan Wühl RSS

    Aggr Above

    Hans Müller

      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

          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
              Hans Müller

              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

                  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

                  • Re: Aggr Above
                    Stefan Wühl

                    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
                        Stefan Wühl

                        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

                          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.