topic Agrregated Data in QlikView

Agrregated Data

dunnalahk123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Can some one help me in writing agg function.

i have a metric which i have to calculate Avg of days for each project ID.

So wrote below aggregated function

Num(Avg(Aggr(Avg([Numvalue]), [PROJECTID])),'#,##0' )

so when i validate for single project ID it exactly matches with source data. but when i check over all data avg is not matching.

below is the snapshot

If i export it to excel and do average i am getting 114. but as per above it is showing 118. we should get 114.

Re: Agrregated Data

sunny_talwar — Tue, 09 Oct 2018 11:22:17 GMT

Your aggr is using PROJECTID as dimension, where as your chart is using Metric and FY_MonthName as the dimension... I think the two will most likely give a different result because avg over different dimension will only give the same number if they have one-to-one relation.

Re: Agrregated Data

dunnalahk123 — Tue, 09 Oct 2018 12:22:30 GMT

Hi Sunny,

Thank you.

if i try with below will it works

Num(Avg(Aggr(Avg([Numvalue]), [PROJECTID],FY_QM,FY_Month,FY_MonthName,FYear)),'#,##0' )

Re: Agrregated Data

sunny_talwar — Tue, 09 Oct 2018 12:24:43 GMT

I am not sure, it might still not because you don't have PROJECTID in your table... If this is a straight table, why don't you use Avg of Rows to get the correct Avg instead of using Aggr()?

Re: Agrregated Data

dunnalahk123 — Tue, 09 Oct 2018 12:51:59 GMT

HI Sunny,

i tried giving normal Avg as per below but still it is not matching as per snpashot

when i export to excel and checked it is giving me 147.8 but qlikview it gives me 143 as per snapshot

Num(Avg([E2E Deployment TAT – Hold]),'#,##0' )

how can we add metric to that expression

Metric={'E2E Deployment TAT – Hold'}

Re: Agrregated Data

sunny_talwar — Tue, 09 Oct 2018 12:59:19 GMT

Did you check "Average of Rows" under Total Mode on the Expressions tab?

Re: Agrregated Data

dunnalahk123 — Tue, 09 Oct 2018 14:27:08 GMT

Hi Sunny,

That Works 🙂

Re: Agrregated Data

sunny_talwar — Tue, 09 Oct 2018 14:29:16 GMT

Superb

Re: Agrregated Data

dunnalahk123 — Tue, 09 Oct 2018 14:32:17 GMT

You are the Champion Sunny 🙂 🙂