Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
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 (3)
1 Solution

Accepted Solutions
Partner
Partner

13 Replies
Partner
Partner

i cant see any flag to indicate if its an additional order or not. i feel you will need to create one if its not there.

something like below.  (previous function gets the record from row before current row during loading)

load *,

 if (previous(order) = order and previous(time)<>time, 1,0) as AdditionalOrderflag

from

... order by Order, time asc

basically i am checking if order number is the same and if time (ordered ascending) has changed which means its an additional order.

you may need additional conditions based on your logic. but hope this points you in right direction

Contributor III
Contributor III

Hi Dilipranjith,
Thanks for your pointer, but I'm not sure if I understand your solution. 

As the nr. in Document  is increasing within an Order for any additional request, I hoped to exclude the lowest document nr. in an Order  (the original order) from the set analysis.

Something like Sum({$<[Movement type]={$(=201)},Warehouse={'L833'}, for all documents except the lowest document nr in the order>}[Qty])

What you're proposing is to create an additional flag in the load script. Is this necessary?

 

Partner
Partner

that is the most efficient way in my opinion.

there maybe more convoluted ways of doing it on the front end through aggr function or similar but this is less efficient than a simple flag

Contributor III
Contributor III

Ok,

Now as a complete n00b with the previous-function, I don't understand the way the formula you've shown works. Apart from the document nr to be used in the formula, it seems as if the formula only looks at the previous record. It seems that it doesn't take into consideration if an additional request contains multiple products or if an order contains multiple documents. Could you elaborate on this? Sorry for all the questions....

 

Contributor III
Contributor III

To all,

I've tried the solution as suggested by dilipranjith. (Please note my previous posts were translated. This post is not. The fieldnames might therefore be different). In the download from excel I've done the following :

[PreBoekingen]:
LOAD
Artikel,
Bewegingssoorttekst,
Boekingsdatum,
Invoertijdstip,
Gebruikersnaam,
"Hvh. in invoer-HE",
Invoerhoevh.eenheid,
Vestiging,
Magazijn,
Bewegingssoort,
Bewegingsreden,
Artikeldocument,
"Positie artikeldoc.",
"Bedrag EV",
Kostenplaats,
"Order",
Reservering

FROM [lib://SAP Data/export020320.XLSX]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate
Boekingen:
LOAD*
Resident PreBoekingen
Order by Order, Artikeldocument, Bewegingssoort, Magazijn;
Drop Table PreBoekingen;

After that I've added the flag as suggested in the qvd:

[Boekingen]:
LOAD
Artikel,
Bewegingssoorttekst,
Boekingsdatum as Datum,
Invoertijdstip as Tijdstip_MM,
"Gebruikersnaam",
"Hvh. in invoer-HE",
Invoerhoevh.eenheid,
Vestiging,
Magazijn,
Artikeldocument,
"Positie artikeldoc.",
Bewegingssoort,
Bewegingsreden,
[Bedrag EV],
Kostenplaats as Kostenplaats_MM,
"Order",
If(Order < 900000000000, Dual('Overige orders', 1),If(Order > 900000000000, Dual('Patientorder', 2))) as [Type order],
If (previous(Order) = Order and previous(Artikeldocument)<>Artikeldocument and [Bewegingssoort]=201 and Magazijn ='L833', 1,0) as Tag_Nabestelling,
Reservering
FROM [lib://QVD Bestanden/Boekingen.qvd]
(qvd);

 

Unfortunately the result is as I suspected.

DatumTijdstip_MMArtikelHvh. in invoer-HEBewegingssoortOrderArtikeldocumentTag_Nabestelling
05-02-20200:16:251037358-120190000217651849008657280
05-02-20200:16:251039336-120190000217651849008657280
05-02-20200:16:251039576-120190000217651849008657280
05-02-20208:34:381011823-120190000217651849008658271
05-02-20208:34:381018907-120190000217651849008658270
05-02-20209:00:05CAR18601-120190000217651849008658531
05-02-202010:36:491013949-120190000217651849008659341
05-02-202010:36:491031972-120190000217651849008659340
05-02-202010:36:491032186-120190000217651849008659340

I would like to have the fields that are indicated with a 0 in the Tag_Nabestelling, to display a 1 as well as these are additional orders to the original as well. Any idea how I can achieve this?

Thanks for your help.

 

MVP
MVP

You can try excluding lowest document like:

Sum({$<[Movement type]={201},Warehouse={'L833'},  Document -={'$(=Min(Document))'}>}[Qty])

 

Note : This would work only when one order is in scope/selected. Else, you can use IF statement to exclude the same.

Contributor III
Contributor III

Hi Tresesco,

I've tried your solution, but the exclusion doesn't seem to have any effect.

Contributor III
Contributor III

Maybe I'm going about this the wrong way.

What if, instead of trying to flag all the records of multiple different additional Orders and Products, I want to flag all the records of the (original) lowest  Documentnr within an order.

What would the load script look like?

MVP
MVP

The expression:

Sum({$<[Movement type]={201},Warehouse={'L833'},  Document -={'$(=Min(Document))'}>}[Qty])

works for me.

Capture.PNG