Set Analysis in an Aggr Function

[#1]

Hi Community,

I have a set analysis that removes 0 for when i am looking for a min value in a data set, as 0 just means nobody has entered so want to ignore.

It works in the below:

IF(CurrencySelector='Local',
IF(VolIsland='Pack',num(Min ({<[Price Per Pack]={'>0'}>}[Price Per Pack]),'#,##0.00'),
IF(VolIsland='Each',num(Min ({<[Price Per Pack]={'>0'}>}[Price per EA]),'#,##0.00'),
IF(VolIsland='Mg',num(Min ({<[Price Per Pack]={'>0'}>}[Price per MG]),'#,##0.000000')))),

IF(VolIsland='Pack',num(Min ({<[Price Per Pack]={'>0'}>}[Price Per Pack]*[ER Tender]),'#,##0.00'),
IF(VolIsland='Each',num(Min ({<[Price Per EA]={'>0'}>}[Price per EA]*[ER Tender]),'#,##0.00'),
IF(VolIsland='Mg',num(Min ({<[Price Per EA]={'>0'}>}[Price per MG]*[ER Tender]),'#,##0.000000')))))

But does not work in the AGGR function:

min

({<[Price Per EA]={'>0'}>}Aggr(

IF(CurrencySelector='Local',
IF(VolIsland='Pack',num(Min ({<[Price Per Pack]={'>0'}>}[Price Per Pack]),'#,##0.00'),
IF(VolIsland='Each',num(Min ({<[Price Per Pack]={'>0'}>}[Price per EA]),'#,##0.00'),
IF(VolIsland='Mg',num(Min ({<[Price Per Pack]={'>0'}>}[Price per MG]),'#,##0.000000')))),

IF(VolIsland='Pack',num(Min ({<[Price Per Pack]={'>0'}>}[Price Per Pack]*[ER Tender]),'#,##0.00'),
IF(VolIsland='Each',num(Min ({<[Price Per EA]={'>0'}>}[Price per EA]*[ER Tender]),'#,##0.00'),
IF(VolIsland='Mg',num(Min ({<[Price Per EA]={'>0'}>}[Price per MG]*[ER Tender]),'#,##0.000000'))))
)

,[Molecule]

))

Any ideas?

[jonathandienst · Accepted solution]

Hi

If you are evaluating this expression in a chart, you need to include all the chart dimensions in the Aggr function (in addition to Molecule).

You might also need to use selective TOTAL qualifiers in the Min statements. Can't be more specific without a qvw to illustrate the problem in more detail.

Hope that helps

Jonathan