avg(aggr(sum(... issue

[jasmina_karcic]

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

[petter]

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

[jasmina_karcic]

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

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

[petter]

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

[jasmina_karcic]

doesn't work...gets result 0

[jasmina_karcic]

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

[jasmina_karcic]

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....

[jasmina_karcic]

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....:/

[sunny_talwar]

I think this might be a good read on this topic

Average – Which average?