Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

Set Analysis in an Aggr Function

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?

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Set Analysis in an Aggr Function

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
1 Reply
MVP
MVP

Re: Set Analysis in an Aggr Function

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein