topic Re: AGGR AVG for occupation rate in QlikView

AGGR AVG for occupation rate

Anonymous — Mon, 08 Oct 2018 16:23:46 GMT

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

Re: AGGR AVG for occupation rate

vishsaggi — Mon, 08 Oct 2018 16:36:05 GMT

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.

Re: AGGR AVG for occupation rate

Anonymous — Mon, 08 Oct 2018 16:42:52 GMT

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

Re: AGGR AVG for occupation rate

sunny_talwar — Mon, 08 Oct 2018 17:16:42 GMT

Try this may be

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

Re: AGGR AVG for occupation rate

vishsaggi — Mon, 08 Oct 2018 17:49:41 GMT

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$);