Aggregation on Date with null measure values

[#1]

Hello everyone,

I wanted to perform an aggregation calculation (an average) in a table based on the values of EPT (= FTE) for a given year and version. I have made the following configurations,

but when there are zero values (due to employment starting in the middle of the year or ending before the year's end), the numbers are not aggregated for the entire year but only for the non-zero months.

Is there anyone who could assist me in solving this issue without having to replace the zero values with actual zeros, please?

Have a good day

[N1kh1l]

matthieu.roux

I think I understand your issue now. I read id differently in my initial attempt.

matthieu.rouxTo be honest, I am still not clear of what according to SAC is NO_DATA and what is NULL

To test it, I did a NULL CHECK ( ISNULL() ) on the measure and could not see that the empty values on right are considered as NULL but for some reason the Average aggregation ignores it.

When I do a IF (ISNULL(measure),0,[measure]) and then do an Average of this, I get the right Average

I would suggest you raise this to SAP and see if they think this is a bug or explain what is NO_DATA and NULL in context of Average.

Br.

Nikhil

[RouxMa]

Thanks for your reply Nikhil but :

- firstly, my initial settings work in some cases (see below);

- secondly, why is it possible to make aggregations specifying that we want exclued null (or zero) values if nulls are not automatically taken into account?

This seems to me to be nonsense or a bug?

Does anayone have an explanation, please ?

[N1kh1l]

matthieu.roux

The average calculated measure will work only for non null values. What you can try as a work around is to create another measure using this Average measure as below and use them in the report

Output: You can see Avg shows value only at non null values but Avg1 shows for all periods.

Hope this helps

Br

Nikhil

[RouxMa]

Thanks for your answer Nikhil but I am not convinced (see my reply with details below).