topic How to get a min value in QlikView

How to get a min value

kunkumnaveen — Thu, 26 Apr 2018 08:51:59 GMT

Hello All,

I Need a a help in writing down a expression ,Below is my Output table

Dimensions :Material Code,Plant Code,Vendor Code,Inco Terms ,Price

Expressions :

Adjusted Price:

if(ID_INCO1='001' or ID_INCO1='002',avg({<SCOPE={'Yes'}>}PRICE) - sum({<Cost_Type={'Packaging'}>}AMOUNT)

avg({<SCOPE={'Yes'}>}PRICE) - sum({<Cost_Type={'Packaging'}>}AMOUNT) -sum({<Cost_Type={'Logistics'}>}AMOUNT)

)

In the last column i need to fill it will min price of that particular Material Code of Adjusted Price irrespective of vendor or plant ,so in the above table i need to fill all the three rows with 1068.76 price.

so i tried below expression to get min Price :

Minumum Price:

if(ID_INCO1='001' or ID_INCO1='002',

min( aggr(avg({<SCOPE={'Yes'}>}PRICE) - sum({<Cost_Type={'Packaging'}>}AMOUNT),[MATERIAL CODE]))

min( aggr(avg({<SCOPE={'Yes'}>}PRICE) - sum({<Cost_Type={'Packaging'}>}AMOUNT) - sum({<Cost_Type={'Logistics'}>}AMOUNT),[MATERIAL CODE]))

)

the above expression giving me some random output ,

Thanks

Naveen

Re: How to get a min value

kunkumnaveen — Thu, 26 Apr 2018 09:14:08 GMT

I think I need to use Total function in that min price expression but I don't known where to write total function in that minimum price expression

Re: How to get a min value

sunny_talwar — Thu, 26 Apr 2018 11:43:46 GMT

May be this

Min(TOTAL <[Material Code]> Aggr(

If(ID_INCO1 = '001' or ID_INCO1 = '002', Avg({<SCOPE={'Yes'}>} PRICE) - Sum({<Cost_Type={'Packaging'}>}AMOUNT),

Avg({<SCOPE={'Yes'}>}PRICE) - Sum({<Cost_Type={'Packaging'}>}AMOUNT) - Sum({<Cost_Type={'Logistics'}>}AMOUNT))

, [Material Code], [Plant Code], [Vendor Code]))

Re: How to get a min value

kunkumnaveen — Thu, 26 Apr 2018 12:37:14 GMT

Thanks Sunny....it worked