topic Re: avg(aggr(sum(... issue in App Development

avg(aggr(sum(... issue

jasmina_karcic — Mon, 02 Jul 2018 12:41:49 GMT

Hi everyone,

I have a table:

I need to calculate average value (or mean) for these values in table, column 1.

I make one measure:

AVG(AGGR(FABS(SUM({$<DATUM={"<=31.5.2018",">=1.5.2018"}, IZVESTAJ={"MIS_COR"}, KLAS_KLIJENTA-={"1","2"}, BROJ_REDA={"1"}>}STANJE_KM/(1000))),DATUM))

I should get value: 262 141

But I am getting this value:

Any help, please?

Jasmina

Re: avg(aggr(sum(... issue

petter — Mon, 02 Jul 2018 13:22:47 GMT

Do you have any 0 values that shouldn't be taken into consideration for the average calculations?

Re: avg(aggr(sum(... issue

jasmina_karcic — Mon, 02 Jul 2018 13:25:34 GMT

hmmm I am thinking where can be this....

But if i made aggr by DATE how is possible for null values

Re: avg(aggr(sum(... issue

petter — Mon, 02 Jul 2018 13:44:44 GMT

Null values are not the same as 0 (zero) values.

null value is a non-existing value or a placeholder for that and will not be taken into consideration for an average.

A 0 values willl be counted as a value.

You could also have negative values in you underlying data - if what you are showing is multiple KMs per date...

If you want to be sure that you only count and handle valid KMs then

Sum( {<KM={">0"}>} KM) and see if the table give the same numbers and total sum.

Likewise you can add this condition to your average calculation:

AVG(AGGR(FABS(SUM({$<STANJE_KM={">0"},DATUM={"<=31.5.2018",">=1.5.2018"}, IZVESTAJ={"MIS_COR"}, KLAS_KLIJENTA-={"1","2"}, BROJ_REDA={"1"}>}STANJE_KM/(1000))),DATUM))

Re: avg(aggr(sum(... issue

jasmina_karcic — Mon, 02 Jul 2018 13:54:34 GMT

doesn't work...gets result 0

Re: avg(aggr(sum(... issue

jasmina_karcic — Mon, 02 Jul 2018 14:04:10 GMT

If I put script:

AVG(AGGR(FABS(SUM({$<STANJE_KM={">0"},DATUM={"<=31.5.2018",">=1.5.2018"}, IZVESTAJ={"MIS_COR"}, KLAS_KLIJENTA-={"1","2"}, BROJ_REDA={"1"}>}STANJE_KM/(1000))),distinct DATUM))

I get 255 306....

But this still is not average value

Re: avg(aggr(sum(... issue

jasmina_karcic — Mon, 02 Jul 2018 14:29:19 GMT

Something in DATE is not okay.

If i put range from 1.1.2018 to 30.4.2018

or from 1.1.2018. to 31.5.2018

or other...not important, it gives the same result....

Re: avg(aggr(sum(... issue

jasmina_karcic — Tue, 03 Jul 2018 07:18:06 GMT

When I write

FABS(SUM({$<DATUM={"<=31.5.2018",">=1.5.2018"}, IZVESTAJ={"MIS_COR"}, KLAS_KLIJENTA-={"1","2"}, BROJ_REDA={"1"}>}STANJE_KM/(1000)))/

count(DATUM={"<=31.5.2018",">=1.5.2018"}, IZVESTAJ={"MIS_COR"}, KLAS_KLIJENTA-={"1","2"}, BROJ_REDA={"1"}>}distinct DATUM)

it works....:/

Re: avg(aggr(sum(... issue

sunny_talwar — Wed, 04 Jul 2018 23:16:57 GMT

I think this might be a good read on this topic

Average – Which average?