topic Find min and max value in a column in QlikView

Find min and max value in a column

Tue, 11 Oct 2011 15:23:51 GMT

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 ?

Find min and max value in a column

Tue, 11 Oct 2011 16:57:03 GMT

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.

Re: Find min and max value in a column

Mon, 17 Oct 2011 17:20:59 GMT

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!

Re: Find min and max value in a column

Mon, 17 Oct 2011 17:31:25 GMT

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.