Find min and max value in a column

[#1]

Hi all,

I have a question (thanks in advance for any suggests), my problem is quite similar to this one:

http://community.qlik.com/thread/7272

But it has more difficulty...

I have two dimension (first one: product, secondo one: area map) and one expression "average price" ,  my goal is to find minimum and maximum "average price" product by product.

In first attachment ( productCorrect.PNG ) you can see how my program works with only one product selected, the expression that colors is the following one:

=if(

    sum(QUANTITY*PRICE)  / sum (QUANTITY) = 

    MAX( total aggr(sum(QUANTITY*PRICE)  / sum (QUANTITY),[AREA MAP]) ),LightRed(),

        if(

        sum(QUANTITY*PRICE)  / sum (QUANTITY)=

        MIN(total aggr(sum(QUANTITY*PRICE)  / sum (QUANTITY),[AREA MAP])),LightGreen()

        )

)

but it works only with a product selected ...

Do you think that there is same error in aggr fuction or i need a set analaysis ?

[#2]

Create two variables vMaxPrice and min price as below:

vMaxPrice: = MAX(aggr(sum(QUANTITY*PRICE)/sum (QUANTITY),[AREA MAP]))

vMinPrice: = MIN(aggr(sum(QUANTITY*PRICE)/sum (QUANTITY),[AREA MAP]))

Write expression as below:

if(sum(QUANTITY*PRICE)/sum(QUANTITY)=vMaxPrice,LightRed(),

          if(sum(QUANTITY*PRICE)/sum(QUANTITY)=vMinPrice,LightGreen())

Hope this helps,

Kiran.

[#3]

Hi Kiran,

first of all thanks for your suggest. Unfortunately it doesn't work.

Actualy if i select only one product, it works.

I create also a column "Max", where i want to insert for each rows the max for product1, but using this expression:

Max( aggr(sum({<PRODUCT_CODE = PRODUCT_CODE>}QUANTITY*PRICE)  / sum ({<PRODUCT_CODE = PRODUCT_CODE>}QUANTITY),[AREA MAP],PRODUCT_CODE) )

it return me the value i need only in summary ( 4.25 euro) and for each row... any other suggest? Any guide where I can learn sothing about partial sum in a pivot table with more than one dimension?

Thanks a lot in advance!

[#4 · Accepted solution]

In that case, Your first expression is right expect for:

MAX( total <ProductCode> aggr(sum(QUANTITY*PRICE)  / sum (QUANTITY),[AREA MAP],ProductCode) ).

You can use total by product code which will create a max avg price for each product, where as total will return you only one value for all columns.

Post a sample application if you have any problems.

Hope this helps,

Kiran.