6 Replies Latest reply: Jul 13, 2016 9:45 AM by Massimiliano Celaschi RSS

    Aggregations, cross tables, and other issues

    Massimiliano Celaschi

      I am trying to calculate the so-called open position of a requirement  (for example, electric energy), specified for each day of a year d.

      In order to decrease the requirement I can buy, at the date A¸ futures MTd(A) (actually, not by single days: I usually buy months, quarters, or years).

      I also attach a monthly dimension m=month(d) to tables PV and MT, so that the initial tables of atomic data are

      PV: d(date)| m(integer)| PV(float)

      MT: d(date)| m(integer) | A(date)| MT(float)

      The open position a day D (i.e. the part of future requirements not still matched) is then defined by

      OP(D,m) = S(d in m; d>D) PVd  S(A<D)S(d in m; d>D)MTd(A)                                                    (1)

      Of course, if D >= last(m), then OP(D,m) = 0.

      If I have no MTs, so no needs of conditions on A, the expression

       

      = sum(TOTAL<m>PV)  - RangeSum(Above(TOTAL PV, 0, NoOfRows(TOTAL)))      (2)

       

      does the trick. It would give the open position decreasing only as a result of moving time.

       

      Can you give me any hints about the methods I can implement the expression (1)?

      I am currently checking the opportunities offered in Qlik Set Analysis, but of course any solution will be warmly welcomed.


      Thanks

      Massimiliano

        • Re: Aggregations, cross tables, and other issues
          Sunny Talwar

          May be share some sample data with the expected output so that we can understand what your expression 1 is doing

            • Re: Aggregations, cross tables, and other issues
              Massimiliano Celaschi

              Thanks for your early reply.

              For instance, suppose for simplicity we are on 30/12/2016 of a year Y, that our requirements are 10 units/day, and that we bought a future of 1 units/day for the month of March. Besides, on 03/02/2017, we happen to buy a further future of 1 units/day for the same month of March.

              So the expected output would be

               

              Date/month

              1

              2

              3

              4

              5

              6

              7

              8

              9

              10

              11

              12

              01/01/2017

              300

              280

              279

              300

              310

              300

              310

              310

              300

              310

              300

              310

              02/01/2017

              290

              280

              279

              300

              310

              300

              310

              310

              300

              310

              300

              310

              03/01/2017

              280

              280

              279

              300

              310

              300

              310

              310

              300

              310

              300

              310

              04/01/2017

              270

              280

              279

              300

              310

              300

              310

              310

              300

              310

              300

              310

               

               

               

               

               

               

               

               

               

               

               

               

              31/01/2017

              0

              280

              279

              300

              310

              300

              310

              310

              300

              310

              300

              310

              01/02/2017

              0

              270

              279

              300

              310

              300

              310

              310

              300

              310

              300

              310

              02/02/2017

              0

              260

              279

              300

              310

              300

              310

              310

              300

              310

              300

              310

              03/02/2017

              0

              250

              248

              300

              310

              300

              310

              310

              300

              310

              300

              310

              04/02/2017

              0

              240

              248

              300

              310

              300

              310

              310

              300

              310

              300

              310

               

               

               

               

               

               

               

               

               

               

               

               

               

              That reads :

              At 01/01/2017, January open position is the requirements of the month minus the requirement of 01/01 (which is considered used). Other months open positions are the full monthly requirements, excepting March, for which we have already secured 31 units and then its open position is 310 - 31 = 279 units. January open position decrease day by day, while other months remain unchanged.

              On 01/02/2012,  January open position is by now zero, and the February open position begins to decrease. On 03/02/2017 I secure other 31 units for March (1 unit/day for the 31 days of March) by the future product, so March open position suddenly decreases by 31 units, and its open position becomes 279 - 31 = 248 units.

               

              Massimiliano