Amplitude in Qlikview

[#1]

Hi,

I have a calculation problem in a QlikView object.

In my table, I have different job descriptions with a minimum class and a maximum class assigned to each of them. I have another table where there is a minimum and a maximum salary for each class (classes are the link between the two tables).

Table 1	Assistant 13-09
Table 2	Minimum salary class 13  = 10 000 Maximum salary class 13 = 20 000 Minimum salary class 09 = 15 000 Maximum salary class 09 = 25 000

I would like to calculate the amplitude of the salary for an Assistant 13-09, which is the difference between the minimum salary of class 13 and the maximum salary of class 09 (10'000-25'000).

What I want:

Job description	Min class	Max class	Min salary	Max salary	Amplitude
Assistant	13	09	10 000	25 000	150%

The problem is that if I use the max class as a link between the two tables, QlikView only calculates the min and max salary of the max class (15'000-25'000) and it's the same if I use the min class (10'000-20'000). If I use both, the result gives me only job descriptions with a single class (for example Secretary 12-12).

What Qlikview makes:

Job description	Min class	Max class	Min salary	Max salary	Amplitude
Assistant	13	09	15 000	25 000	66,7%

My expression is: "=([maximum salary]-[minimum salary])/[minimum salary]"

Do you have an idea to help me? Thank you.

[pokassov]

Hi!

Your expression should be: "=(max([maximum salary])-min([minimum salary]))/min([minimum salary])"

[MarcoWedel]

please post a sample application

thanks

regards

Marco

[#4]

Sorry I don't know how to link the qvw file so it's just a copy paste...

PDP:

LOAD

     [N° salarié]&''&[N° dossier] as [IDperso],

     [Libellé fonction salarié]

FROM

(ooxml, embedded labels, header is 1 lines, table is sheet1);

Left Join

LOAD

     [Salarié n°]&''&[Dossier n°] as [IDperso],

     Classe,

     [Classe max.],

     [Classe min.],

     [Min-Max]

FROM

(ooxml, embedded labels, header is 1 lines, table is sheet1);

PDP2:

LOAD *,  

  if (IsNull([Classe max.]),0,[Classe max.]) as [maximum actuel]

// if (IsNull([Classe min.]),0,[Classe min.]) as [minimum actuel]

Resident PDP;

Left join

LOAD

  [Classe min] as [minimum actuel],

  Minimum as [minimum actuel CHF],

  Classe as [maximum actuel],

  Maximum as [maximum actuel CHF]

FROM

(ooxml, embedded labels, table is [QV]);

Drop table PDP;