topic Re: Aggr Function in QlikView

Aggr Function

lohitala — Wed, 25 Nov 2020 16:16:04 GMT

Hi All, could you please help me out with this expression where i am going wrong.

In the chart it show the same values that is not expected.

=count(Aggr(if($(=num(count( {<TopBox={'1'},FiscalYear={'2018'}>}SRID)/count( {<FiscalYear={'2018'}>}SRID),'#.00000'))>=percent1,QuestionLabel),percent1,QuestionLabel,FACILITY,Srnumber1))

Re: Aggr Function

jcampbell474 — Fri, 29 Jun 2018 17:30:57 GMT

Are you intentionally aggregating on percent1,QuestionLabel,FACILITY,Srnumber1, because they're not a dimension (w/the exception of FACILITY)?

I'm thinking you've aggregated w/too many fields. You can try removing one aggregation at a time and see if your values change.

HTH,

Jason

Re: Aggr Function

lohitala — Fri, 29 Jun 2018 19:24:49 GMT

Actually those are all Dimension field.

I have to aggregated it those level i think there is problem with dollar expansion in Aggr function.

Re: Aggr Function

Anil_Babu_Samineni — Sat, 30 Jun 2018 05:43:04 GMT

How about this? I am assuming percent1 is not the field?

=count(Aggr(if(num(count( {<TopBox={'1'},FiscalYear={'2018'}>}SRID)/count( {<FiscalYear={'2018'}>}SRID),'#.00000')>=percent1,QuestionLabel),percent1,QuestionLabel,FACILITY,Srnumber1))

Re: Aggr Function

rubenmarin — Sat, 30 Jun 2018 07:31:46 GMT

Hi Lohit, dollar expansion is processed before the table is calculated, so the stepsa are: this dollar expansion is calculated, returns a number, and this number replace the dollar expansion in expression, so all dimensions uses the same numbers, ie:

=count(Aggr(if(0.8>=percent1,QuestionLabel),percent1,QuestionLabel,FACILITY,Srnumber1)) // 0.8 just as an example of how the expression is expanded.

Anil expression below should work.

Re: Aggr Function

jcampbell474 — Mon, 02 Jul 2018 12:21:16 GMT

Sorry, I wasn't clear. Yes, they are dimensions, just not dimensions in the chart you're referencing. I initially thought your were forcing aggregation outside of the chart dimensions.