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

# Aggregations, cross tables, and other issues

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

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

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 …

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

• ###### Re: Aggregations, cross tables, and other issues

I kind of understand the output you are looking for, but what all is available to you as input?

• ###### Re: Aggregations, cross tables, and other issues

The avalaible data are the tables PV and MT specified in the first post.

The date field d, and also the integer field m (which is however calculated from the former), can be considered as Syn key.

• ###### Re: Aggregations, cross tables, and other issues

I am still not 100% sure how your data will look like. I really wish to help, but unless I understand, I won't be guide you in any direction.

• ###### Re: Aggregations, cross tables, and other issues

I managed to work the result out in Access by nested queries.

For example, provided I created a cartesian product [Giorno X Mese] (366 * 12 items in 2016), by

SELECT D, M,

(select sum(PV_1.PV) from PV as PV_1 where PV_1.m = M and PV_1.d >  D) AS MWh

FROM [Giorno X mese];

I get the base requirement, i.e. the equivalent of expression (2) in the first post, and by

SELECT D, m,

(select sum(MT_1.MT) from [MT] as MT_1 where MT_1.m = M and MT_1.d > D and MT_1.A < D) AS MWh

FROM [Giorno X mese];

I get the secured quantity in each day for each month.

Joining the two results and subtracting the latter from the former give the result, but the price of cumbersome expressions and expensive computations.

I wonder whether I can find a method in QV equipollent to the previous nested queries.