Agrregated Data

[dunnalahk123]

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.

[sunny_talwar]

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.

[dunnalahk123]

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' )

[sunny_talwar]

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()?

[dunnalahk123]

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'}

[sunny_talwar · Accepted solution]

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

[dunnalahk123]

Hi Sunny,

That Works  🙂

[sunny_talwar]

Superb

[dunnalahk123]

You are the Champion Sunny 🙂 🙂