Set Analysis in an Aggr Function

Thu, 19 Jul 2012 13:57:50 GMT

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?

Re: Set Analysis in an Aggr Function

jonathandienst — Thu, 19 Jul 2012 14:27:22 GMT

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

topic Re: Set Analysis in an Aggr Function in QlikView

Set Analysis in an Aggr Function

Re: Set Analysis in an Aggr Function