topic Calculating averages over a month in QlikView

Calculating averages over a month

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

Quick question involving an average percentage calculated both as a whole and over a month.

I've tried two expressions to calculate the averages, but neither works for both scenarios.

'Utilisation Overall' Expression is

Sum([Billed Hours])/sum([Location Available  Hours])

'Utilisation MBM' Expression is

Sum([Billed Hours]/[Location Available  Hours])

Scenario 1: No month selected.

Overall expression works fine but MBM does not.

Scenario 2: Month selected.

MBM expression works fine but overall does not.

Is it possible to use a single expression which works for both scenarios?

Many Thanks,

Joe

sunny_talwar — Thu, 18 Aug 2016 16:15:12 GMT

How about like this:

If(GetSelectedCount(Month) >0, Sum([Billed Hours]/[Location Available Hours]), Sum([Billed Hours])/sum([Location Available Hours]))

Thu, 18 Aug 2016 16:26:07 GMT

Exactly what I was looking for - It's been a long day...!

Thanks very much

Joe

sunny_talwar — Thu, 18 Aug 2016 16:29:17 GMT

No problem at all