Count shipment with weight between 1000 and 3000

[ville]

Hi everybody 

Tried my luck reading through the forum but couldn't find or understand the right solution for my problem. I am new to qlik to bear with me. 

I need to count the amount of shipments between 1000 and 3000g per partner. Idee is to get a table with the partners as a dimension.

The tricky part is to calculate the amounts of shipments in the weight range (1000-3000g). Shipment weights needs to be calculated over different datasets. All shipments have a unique ID "OrderID-orderID" and a shipment can have multiple items. Would need to calculate the shipment weight via "OrderID-orderID" and "weight". 

I thought it should work this way -> 

aggr(count(distinct[OrderID-orderID] ), Sum( weight, [OrderID-orderID]={">1000<=3000"}))

Tabel names are "Orders" and "OrderItems"

Any ideas where I went wrong? 

Seems I forgot/didn't get something, as it doesn't work. 

[jfkinspari]

Could you add a sample application?

[ville]

Sure, sample file is added .  In the file you also see, that there are a lot more DB connected. This is just to show how the link is done to the delivery company from the order. Hope it helps. 

Thanks for the support! 

[JordyWegman · Accepted solution]

Hi Ville,

You can create this formula and use it with the dimension you want:

Count(Distinct {$< weight = {">1000<=3000"} >} OrderID-orderID)

 Jordy

Climber

[ville]

Hi Climber

Thanks that worked perfectly!