AGGR AVG for occupation rate

[Anonymous]

Hello

This looks simple but I am stuck

MJOCC is the number of square meter sold

Superficie dispo is the number of available square meter to sell.

So MJOCC/superficie dispo should never be higher than 100% so I calculated what I call a corrected occupation rate.

Then I fail to get the correct occuppation rate for the 17/01/2019 I get 84% when I want to see 75,7% (see excel attached)

Thanks a lot for your help

[vishsaggi]

Your excel sheet show a field called CorrectedMJooc so may be you have to use that field ? If not your calc is correct which is 84% as your MjoCC and Superficio dispo total are 4061 and 4838 as show in your excel screenshot below.

[Anonymous]

Yes in the excel I did it with the corrected mjocc.

but I also did find the the expected result in L41 using only Corrected occupation rate and Mjocc. I would like to do the same in qlikview

[sunny_talwar]

Try this may be

Sum(Aggr(RangeMin(Sum(Mjocc), Sum([Superficie Dispo])), date_occ_equiv_2019, Année, date_occ, Emplacement))/Sum([Superficie Dispo])

[vishsaggi]

Try this? Then change your sum(Mjocc) to Sum(CorrectedMjocc)

LOAD Année,

     Emplacement,

     date_occ,

     date_occ_equiv_2019,

     [Superficie Dispo],

     Mjocc,

     IF(Mjocc >[Superficie Dispo], [Superficie Dispo],Mjocc) AS CorrectedMjocc

FROM

[..\calcul too.xls]

(biff, embedded labels, table is Sheet1$);