Min(Only()): nested aggregation not allowed

[Anonymous]

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?

[sunny_talwar]

Sounds good

[vishsaggi]

Ok. May be a sample file will help.

[ogautier62]

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

[Anonymous]

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

[sunny_talwar]

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)

)

[Anonymous]

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?

[sunny_talwar]

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

[marcus_sommer]

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