topic Min(Only()): nested aggregation not allowed in QlikView

Min(Only()): nested aggregation not allowed

Anonymous — Fri, 13 Jul 2018 12:57:54 GMT

Dear all,

I have a fields called "RatioNumName" and "RatioDenumName", what I want is to find Min(RatioNumName/RatioDenumName), but ignoring the selections on RatioNumName and RatioDenumName. Eventually RatioDenumName and RatioNumName are the names of the fields, which should be computed.

I have done the following in order to test, if everything is working as expected:

$(vRatioDenum) = '{1} [' & RatioDenumName & ']'

$(vRatioNum) = '{1} [' & RatioNumName & ']',

which seems to be working correctly, but when I say that I need:

Min($(vRatioNum)/$(vRatioDenum)), then no result.

I thought further and came with following idea:

Min($(=$(vRatioNum))/$(=$(vRatioDenum))), but again - no chances.

The last, I have tried: Min(Only({1} RatioNum)/Only({1} vRatioDenum)), but I get an error that "Nested aggregation not allowed", Does anyone has any clue, where the issue is?

Re: Min(Only()): nested aggregation not allowed

olivierrobin — Fri, 13 Jul 2018 15:12:14 GMT

hello

you should have

min({1}RatioNum/RatioNum)

the set analysis apply to the grouping function, not to each of the operands

Re: Min(Only()): nested aggregation not allowed

sunny_talwar — Fri, 13 Jul 2018 15:17:37 GMT

How about this

$(vRatioDenum) = '[' & RatioDenumName & ']'

$(vRatioNum) = '[' & RatioNumName & ']',

Min({1} $(vRatioNum)/$(vRatioDenum))

Re: Min(Only()): nested aggregation not allowed

Anonymous — Fri, 13 Jul 2018 15:32:56 GMT

Nope, does not do the trick, unfortunately.

Re: Min(Only()): nested aggregation not allowed

Anonymous — Fri, 13 Jul 2018 15:33:26 GMT

I understand that, but it doesn't work that way

Re: Min(Only()): nested aggregation not allowed

sunny_talwar — Fri, 13 Jul 2018 15:34:53 GMT

Would you be able to share a sample?

Re: Min(Only()): nested aggregation not allowed

Anonymous — Fri, 13 Jul 2018 15:36:12 GMT

Need to think about that in order to come up with an example, since it is not straight forward. Will try to do later

Re: Min(Only()): nested aggregation not allowed

vishsaggi — Fri, 13 Jul 2018 15:36:54 GMT

Can we add this way Sunny:

= Min({1} $(=vRatioNum)/$(=vRatioDenum))

Re: Min(Only()): nested aggregation not allowed

sunny_talwar — Fri, 13 Jul 2018 15:38:24 GMT

Worth giving it a shot

Re: Min(Only()): nested aggregation not allowed

Anonymous — Fri, 13 Jul 2018 15:40:20 GMT

Doesn't evaluate to a result(

Re: Min(Only()): nested aggregation not allowed

sunny_talwar — Fri, 13 Jul 2018 15:44:00 GMT

Sounds good

Re: Min(Only()): nested aggregation not allowed

vishsaggi — Fri, 13 Jul 2018 16:25:26 GMT

Ok. May be a sample file will help.

Re: Min(Only()): nested aggregation not allowed

ogautier62 — Sun, 15 Jul 2018 08:51:51 GMT

Hi,

maybe :

use aggr because of nested not allowed

and add {1} everywhere, after min( too

Min({1} aggr(Only({1} RatioNum),your dimension)/aggr(Only({1} vRatioDenum),your dimension) )

regards

Re: Min(Only()): nested aggregation not allowed

Anonymous — Tue, 24 Jul 2018 14:44:25 GMT

stalwar1‌‌, here is a sample file, where output table denotes what results should be at the end in the ratiocompute table !

Re: Min(Only()): nested aggregation not allowed

sunny_talwar — Tue, 24 Jul 2018 14:53:38 GMT

Unfortunately, this won't be possible because dollar sign expansion is expanded at a chart level and not at a row level... when nothing is selected.... Numerator can take two values (Field1 or Field2) on different rows.... where as Denominator can take 3 values (Field2, Field3, Field4) on different rows.... So, when nothing is selected... this won't work...

but when you select a single row is selected (or group of rows with same Numerator and Denominator)... for example Ratio1... it will start working

What you can do is to use Pick(Match()) or If statement to do this... it doesn't look nice.... but it works

Sum(

Pick(Match(Numerator, 'Field1', 'Field2', 'Field3', 'Field4'), Field1, Field2, Field3, Field4)

Sum(

Pick(Match(Denominator, 'Field1', 'Field2', 'Field3', 'Field4'), Field1, Field2, Field3, Field4)

)

Re: Min(Only()): nested aggregation not allowed

Anonymous — Tue, 24 Jul 2018 15:03:15 GMT

Thanks for the suggestion. But, in reality I have like 14 fields and this will look like a mess. Maybe there are some better ways on how to match the field name and with the text in the cell?

Re: Min(Only()): nested aggregation not allowed

sunny_talwar — Tue, 24 Jul 2018 15:06:57 GMT

From what I know... this won't be possible... but may be marcus_sommer‌ or marcowedel‌ have ideas

Re: Min(Only()): nested aggregation not allowed

marcus_sommer — Wed, 25 Jul 2018 16:44:34 GMT

Sunny is right - a $-sign expansion won't directly work on row-level and the pick(match()) approach is already the most elegant way if you really need such a construct. Whereby it could be even done dynamically if you keep the parts within a table and used something like:

...

Pick(Match(Numerator, '$(=concat(FieldList1, chr(39) & ',' & Chr(39)))'),

$(='[' & concat(FieldList1, '],[') & ']'))

...

- Marcus