topic Re: maximum and minimum between periods in QlikView

maximum and minimum between periods

Tue, 09 Jul 2013 07:49:38 GMT

Hello:

I am new to Qlikview and I have a very simple problem, that is:
I want to calculate the maximum and minimum values within a selected date range and I do not get any value using the expressions:

maximum value: Aggr (Max (Máximo), Ticker, Mes_Vencimiento)
minimum value: Aggr (Min (Minimo), Ticker, Mes_Vencimiento)

I tried putting the date fields in the expression but neither works. I do not generate any results.

Instead if I calculated the initial value at the opening of the first day of the date range and the closing value of the last day of the date range.

With the following expressions:

FirstSortedValue (Apertura,Fecha)
FirstSortedValue (Cierre,-Fecha)

The data structure is as follows:

The values that I had to figure can be seen in the accompanying table.

For example the maximum period of the DJI is 4772.56 corresponding to the day 08.02.1995 of the selected period and the minimum would be the value 4587.66 corresponding to the day 17/8/1995

I also like to know how to calculate the days between these dates with respect to the beginning of the period or at the end of the period.

Greetings and thank you very much.

Re: maximum and minimum between periods

simondachstr — Tue, 09 Jul 2013 08:21:55 GMT

1.) I think the problem is that your decimal seperator is set to ',' while your index data contains a dot as a dec seperator. This way, numerical functions like max() will not evaluate properly.

2.) For calculating the days between these dates I suggest you use something like this:

=networkdays (Only({<Maximo=$(=max(Maximo))>} Fecha), Only({<Minimo=$(=min(Minimo))>} Fecha)) + networkdays (Only({<Minimo=$(=min(Minimo))>} Fecha), Only({<Maximo=$(=max(Maximo))>} Fecha))

I summed networkdays because you need to consider two cases:

1) the min is earlier than the max

2) the max is earlier than the min

I haven't tested the above formula so small adjustments will probably be necessary.

Writing a formula which considers the right amount of holidays for each index will be a challenge.

Re: maximum and minimum between periods

Tue, 09 Jul 2013 09:39:48 GMT

Thank you very much for the prompt reply, I tried changing the decimal formats and works properly maximum and minimum functions.

The calculation of days between these values will try with the formulas recommended