Why does MIN value from an AGGR expression return a 0 when no value are equal to zero

[rviel_dubo]

Hi everyone,

I've got an aggr function that should return the maximum and minimum number of order managed per month for the last 24 months.

The max works fine (19510) but the minimum returns 0, see example below.

I'm getting gray hair over this so please any suggestions will help! 🙂

Thanks,

Robert V.

[sunny_talwar]

Inner set analysis force the expression to be 0 for Months which are not displayed... hence Min will pick it... but Max will not pick unless all other values are less than 0. Does that make sense?

[rviel_dubo]

Honestly no but that's something I'll have to live with.
I've always believed that aggr was creating an inner table for the min function with 24 rows (months) of values not equal to zero. I've never considered that the billion years not selected would be set to zero and worst would be picked.
I'm just glad that I've learned something new today.
Thanks again for your time and your explication.

Regards,
Robert V.
Thanks again for your time and your expl

[sunny_talwar]

I guess you understood what it is doing, but not why... Aggr is doing that, but the thing to understand here is that in your virtual table... the expression is constrained to the last 24 months, but not the Dimension... Although you see this

Jun 2017 - 16765
Jul 2017 - 13482
...
and so on

But truly the chart is

...
Apr 2017 - 0
May 2017 - 0
June 2017 - 16765
Jul 2017 - 13482
...
and so on

I think you can view this by unchecking 'Suppress When Zero' on the presentation tab of the chart properties.... and because when you don't constraint your outer set analysis to the months that you are viewing... you see 0 as Min.

In general it is always good to use the same inner set analysis to the outer set analysis.... there might be times when you would not want to do it, but in my experience... I have never had that situation needed for my own development, but have seen it when helping others.