5 Replies Latest reply: May 18, 2012 7:01 AM by icehorde

# Creating criteria for count of records ?

Hello qlik community,

what i am trying to achieve in my current document is to calcluate the transport cost per kg imported goods. The problem is that the accounting dates for a delivery and transport doesn't match. For examle ( as you will see in the attached file ) i could have 4 deliveries but 3 accounted transports so in order to calculate cost/kg i have to use the quantity of 3 deliveries.

I have two tables, one for deliveries and one for transport. The keys are Code, Date and Month.

I have tried to add new field RowNo and sum transport/kg for matching rows. I sort the two tables by date and code and things work untill transport doens't match deliveries because the two tables have different number of rows. Apparently delivery table has the max number of rows so my idea is to create the transport table with the same number of rows, i.e 4 deliveries and 4 transports with 1 null row but with rowno 4 as in the example .

Thank you

IceHorde

• ###### Creating criteria for count of records ?

Sorry, I don't get what you want.

I see only one possible key here, which is Code. Dates seem to possibly differ between related deliveries and transports? Quantitiy is your measure.

So what are you trying your achieve? Could you give the expected outcome for your posted sample data and also explain how you would calculate it manually? Then probably someone can try to create a data model and expressions out of that.

• ###### Creating criteria for count of records ?

Sorry, i was not quite detailed.

Yes, dates differ between related deliveries and transports. The measure unit for Quantity is kolograms and for Credit is local currency.

 Table1 Date Code Text Quantity 17.04.12 12345 delivery 200 19.04.12 12345 delivery 210 23.04.12 12345 delivery 60 29.04.12 12345 delivery 100

 Table 2 Date Code Text Credit 19.04.12 12345 transport 40 24.04.12 12345 transport 42 25.04.12 12345 transport 12

Table 1 is deliveries , Table 2 is transport. As you can see there are 4 deliveries and only 3 transports for them. Dates differ for relevant deliveries and transport wich means that the delivery from 17.04.12 of 200 kg costs 40 units local currency from 19.04.12 (first delivery for first transport and so on, no matter the date).

If i would do it manually i would sum credit(40+42+12)/ sum quantity (200+210+60).

IceHorde

• ###### Re: Creating criteria for count of records ?

Hi, i've attached a sugestion... I made a single table with concatenation and the  interface with set analysis and a variable to select the date for analysis. Please take a look.

Regards,
Cesar

• ###### Re: Creating criteria for count of records ?

I tried to follow your idea with matching lines by creating a kind of linenumber per code, using autonumber. This ID is always starting from 1 per Code, for both deliveries and transports.

So, in your chart, you can use advanced aggregation to sum up the delivery quantities only up to the linenumber that matches the max transport linenumber.