Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
andy_griffiths01
Contributor II
Contributor II

RangeMin to sum lower of two fields

Hi, im trying to do a calculation where im summing the value of the lower of two fields based on a set of criteria. im trying to replicate this query (From Cognos analytics):

Total( if ( [Purchases].[Purchase Order Details].[Purchase Transaction Type] = 'Purchase Order Line' and [Purchases].[Inbound Service Level Details].[Purchase Delivery Timing] is not null and [Purchases].[Purchase Order Details].[Purchase Transaction Sub-Type] <> 'Open Purchase Order Line' ) then ( [Database View].[FCT_PURCHASE_ORDER_LINE].[LOWER_OF_ORD_OR_REC] ) else ( 0 ) )

However im having huge difficulties. i can create the set analysis ok however when i try to then only pick up the lowest value im getting stuck.

this is the expression im trying:

Rangemin(Sum({$<[Purchase Transaction Type]={'Purchase Order Line'},[Purchase Delivery Timing]={'Early','Late','On Time'},
[Purchase Transaction Sub-Type]-={'Open Purchase Order Line'}>}[Purchase Ordered Qty]),[Purchase Processed Qty])

However the number coming back matching the value in the [Purchase Ordered Qty] column. 

 

ive attached my sample data.

Any ideas how i get round this? 

 

many thanks

 

 

 

 

 

 

2 Replies
Anil_Babu_Samineni

Perhaps this?

If([Purchase Transaction Type]='Purchase Order Line' and Len([Purchase Delivery Timing])>0 and Not Match([Purchase Transaction Sub-Type], 'Open Purchase Order Line'), [LOWER_OF_ORD_OR_REC], 0)

Or

Sum(Aggr(If([Purchase Transaction Type]='Purchase Order Line' and Len([Purchase Delivery Timing])>0 and Not Match([Purchase Transaction Sub-Type], 'Open Purchase Order Line'), [LOWER_OF_ORD_OR_REC], 0), Dim1 Dim2,...))

Or

Alt(Sum({<[Purchase Transaction Type]={'Purchase Order Line'}, [Purchase Delivery Timing]={"=Len([Purchase Delivery Timing])>0"}, [Purchase Transaction Sub-Type] -={'Open Purchase Order Line'}>} [LOWER_OF_ORD_OR_REC]), 0)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
andy_griffiths01
Contributor II
Contributor II
Author

Hi, thanks for the response and the examples.

In my data i dont have [LOWER_OF_ORD_OR_REC] its actually split into two:  [Purchase Ordered Qty] and [Purchase Processed Qty]. its these two fields i need to find the lower of the two.

how do i incorporate that into the below?

Many thanks