topic Re: Why does MIN value from an AGGR expression return a 0 when no value are equal to zero in QlikView

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

rviel_dubo — Tue, 04 Jun 2019 20:30:26 GMT

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.

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

lironbaram — Wed, 05 Jun 2019 05:35:51 GMT

first thing to check is that in your table properties

under presentation tab you uncheck the option suppress zero values

usually i'll assume you have some values that are assigned to null month , so you don't see it in the table

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

rviel_dubo — Wed, 05 Jun 2019 12:28:58 GMT

Hi Lironbaram,

That's the first thing I've looked and that's why I've place a picture of my table along with my request, option is already unchecked and I've got 24 results (24 months rolling). that's why I've having gray hair over this.

Thanks anyway 🙂

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

sunny_talwar — Wed, 05 Jun 2019 13:58:28 GMT

How about this?

Min({<Sales_MonthSEQ = {">-25<0"}>} Aggr( Count({<Sales_MonthSEQ = {">-25<0"}>} DISTINCT OrderNo) , Sales_MonthName) )

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

rviel_dubo — Wed, 05 Jun 2019 13:36:10 GMT

No results, 😞

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

sunny_talwar — Wed, 05 Jun 2019 13:38:06 GMT

Not even showing a 0? Any error messages? Can you show an image of the expression in the expression editor?

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

rviel_dubo — Wed, 05 Jun 2019 13:40:32 GMT

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

sunny_talwar — Wed, 05 Jun 2019 13:44:16 GMT

This looks like the original expression you had which was showing 0

Did you try my suggestion?

Min({<Sales_MonthSEQ = {">-25<0"}>} Aggr( Count({<Sales_MonthSEQ = {">-25<0"}>} DISTINCT OrderNo) , Sales_MonthName) )

Please note that I had a typo on Sales_MonthName field... I have fixed it now... I initially used Sales_Monthname... with a lower case n

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

rviel_dubo — Wed, 05 Jun 2019 13:51:35 GMT

I've just corrected your typo and it works !!!!
Thanks I really appreciate 🙂

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

rviel_dubo — Wed, 05 Jun 2019 14:05:06 GMT

Now if I may ask WHY do I need the extra set analysis before the aggr for the min function and not for the max?

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

sunny_talwar — Wed, 05 Jun 2019 15:40:07 GMT

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?

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

rviel_dubo — Wed, 05 Jun 2019 18:21:08 GMT

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

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

sunny_talwar — Wed, 05 Jun 2019 18:58:23 GMT

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.