Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
kunkumnaveen
Specialist
Specialist

How to get a min value

Hello All,

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

PIC.PNG

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

1 Solution

Accepted Solutions
sunny_talwar

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]))

View solution in original post

3 Replies
kunkumnaveen
Specialist
Specialist
Author

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

sunny_talwar

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]))

kunkumnaveen
Specialist
Specialist
Author

Thanks Sunny....it worked