topic Re: Calculating average in set analysis in QlikView

Calculating average in set analysis

markgraham123 — Tue, 24 May 2016 13:39:53 GMT

(Avg(Aggr(Sum({<[Recent 7 Days] -= {'-'}>}eaches ),[Recent 7 Days]))) /

Avg((Aggr(Sum({<[Recent 7 Days] -= {'-'}>}duration_logged_in_Hrs ),[Recent 7 Days])))

Hi all,

i'm using above script to calculate average of 'eaches' consumed in recent 7 days.

Although it is giving me the average, i do not wanna divide the average eaches in recent 7 days with

average hrs logged in.

I'm trying to divide sum of eaches / sum of logged in hrs. (Still having the condition Recent 7 Days <> '-'

Not working:

(Aggr(Sum({<[Recent 7 Days] -= {'-'}>}eaches ),[Recent 7 Days]))) /

((Aggr(Sum({<[Recent 7 Days] -= {'-'}>}duration_logged_in_Hrs ),[Recent 7 Days])))

Any help is highly appreciated!

swuehl — Tue, 24 May 2016 13:41:32 GMT

Not sure if I understand, but in general, use (outer) aggregation functions

Sum(Aggr(Sum({<[Recent 7 Days] -= {'-'}>}eaches ),[Recent 7 Days]))) /

Sum((Aggr(Sum({<[Recent 7 Days] -= {'-'}>}duration_logged_in_Hrs ),[Recent 7 Days])))

edit: Or maybe you don't need to group by [Recent 7 Days]?

=Sum({<[Recent 7 Days] -= {'-'}>}eaches ) / Sum({<[Recent 7 Days] -= {'-'}>}duration_logged_in_Hrs )

markgraham123 — Tue, 24 May 2016 13:50:41 GMT

Thanks a lot swuehl‌

I learnt a good thing about set analysis