Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
andy_griffiths01
New 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

Re: RangeMin to sum lower of two fields

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
andy_griffiths01
New Contributor II

Re: RangeMin to sum lower of two fields

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