Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dpmbrouwers
Contributor III
Contributor III

SUM question with exclusion

Hi QlikSense friends and wizards,

I need help with the following:

Product

Date

time

Qty

Warehouse

Movement type

Document

Order

1004789

10-2-2020

21:33:31

-3

L833

201

4900869084

900002059299

1004790

10-2-2020

21:33:31

-2

L833

201

4900869084

900002059299

1006181

10-2-2020

21:33:31

-1

L833

201

4900869084

900002059299

1007199

10-2-2020

21:33:31

-1

L833

201

4900869084

900002059299

1009767

10-2-2020

21:33:31

-1

L833

201

4900869084

900002059299

1006132

11-2-2020

8:13:21

-1

L833

201

4900869234

900002059299

1014405

11-2-2020

8:13:21

-1

L833

201

4900869234

900002059299

1033404

11-2-2020

8:13:21

-1

L833

201

4900869234

900002059299

1039535

11-2-2020

8:13:21

-1

L833

201

4900869234

900002059299

CAR03601

11-2-2020

8:13:21

-1

L833

201

4900869234

900002059299

CAR03701

11-2-2020

8:13:21

-1

L833

201

4900869234

900002059299

CAR06101

11-2-2020

8:13:21

-1

L833

201

4900869234

900002059299

CAR09401

11-2-2020

8:13:21

-1

L833

201

4900869234

900002059299

CHE03601

11-2-2020

8:13:21

-1

L833

201

4900869234

900002059299

CAR03601

11-2-2020

8:40:08

-1

L833

201

4900869252

900002059299

CAR03701

11-2-2020

8:40:08

-1

L833

201

4900869252

900002059299

CAR06101

11-2-2020

8:40:08

-1

L833

201

4900869252

900002059299

CAR09401

11-2-2020

8:40:08

-1

L833

201

4900869252

900002059299

Above extract from a data-table shows an Order with an original BOM (Date: 10-2-2020, time 21:33:31, Document: 4900869084) and requests for extra products on this order. The Document nr are always ascending within an Order: the lowest Document is the original BOM

I looking to SUM all the Qty for the additional requests on an Order in this Warehouse and for this Movement type.

In this example: The original Order was for a Qty of 8, the first additional Order was for a Qty of 9, the second additional Order was for a Qty of 4. My desired outcome is 13.

I’m able to SUM all Qty fort his order Sum({$<[Movement type]={$(=201)},Warehouse={'L833'},>}[Qty]), but I don’t know how to exclude the Qty for the original Document within this Order. I'm thinking along the lines of excluding Min(Document), but haven't been able to incoorporate this in my formula.


Please help me.

Labels (2)
13 Replies
dpmbrouwers
Contributor III
Contributor III
Author

Hmmm,

This is interesting: I see the result you're getting, but at my end it seems as the Document -={'$(=Min(Document))'} part of the statement doesn't do anything. I'll do some more digging and let you know.

dpmbrouwers
Contributor III
Contributor III
Author

So...... it seems to work ......and then it doesn't....and then it does. In other words I cannot get it working properly.

I feel a lot for the solution dilipranjith has mentioned: flagging in the load script.

I have ordered the Orders and Document Nr's in ascending order in the Load script, but need to overcome the challenge that the script needs to check an x-amount of records within an Order to determine the lowest value of the Document Nr. I'm guessing that looking for the lowest value is easier than looking for all but the lowest value.

Anyone who can help me out here?

tmathijssen
Partner - Contributor II
Partner - Contributor II

Hi,

See example.

dpmbrouwers
Contributor III
Contributor III
Author

Hi Tom,

That's it. Thanks a million!