SUM question with exclusion

[dpmbrouwers]

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.

[dplr-rn]

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

[dpmbrouwers]

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?

[dplr-rn]

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

[dpmbrouwers]

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....

[dpmbrouwers]

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.

Datum	Tijdstip_MM	Artikel	Hvh. in invoer-HE	Bewegingssoort	Order	Artikeldocument	Tag_Nabestelling
05-02-2020	0:16:25	1037358	-1	201	900002176518	4900865728	0
05-02-2020	0:16:25	1039336	-1	201	900002176518	4900865728	0
05-02-2020	0:16:25	1039576	-1	201	900002176518	4900865728	0
05-02-2020	8:34:38	1011823	-1	201	900002176518	4900865827	1
05-02-2020	8:34:38	1018907	-1	201	900002176518	4900865827	0
05-02-2020	9:00:05	CAR18601	-1	201	900002176518	4900865853	1
05-02-2020	10:36:49	1013949	-1	201	900002176518	4900865934	1
05-02-2020	10:36:49	1031972	-1	201	900002176518	4900865934	0
05-02-2020	10:36:49	1032186	-1	201	900002176518	4900865934	0

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.

[tresesco]

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.

[dpmbrouwers]

Hi Tresesco,

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

[dpmbrouwers]

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?

[tresesco]

The expression:

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

works for me.