16 Replies Latest reply: Jul 1, 2015 8:50 AM by Ruben Marin

# Issue in calculating Turn for multiple dates

Hi Friends,

I've an issue in calculating the turn for two fields.

Lets say I have a field Date1, Date2 where the turn should be calucated for Date1 and Date2

Sample Data

Order NoIterationStartEndTurn
123Iteration123/06/2015 15:42:4624/06/2015 09:11:33

=networkdays(23/06/2015,24/06/2015)

=2

123Iteration 224/06/2015 09:18:2324/06/2015 18:29:17

--Turn 2

=1

123Iteratin 326/06/2015 11:28:0826/06/2015 14:08:18

--Turn 3

=1

123Iteration 426/06/2015 14:36:1226/06/2015 16:35:54

--Turn 4

=1

Average=Average(2,1,1,1)=1.25

But the Start Date and End Date comes from different tables based on Primary Key and iteration is what I added for better understanding of my requirement.

Now i want to calculate the turn at every iteration based on the Order No.

If I try to join these two fields with the Order No, it is doing cross join and populating 16 combinations and the turn metric is being calculated wrongly.

Wanted help on how to deal with such scenarios.

Final I want to see turn as 1.25 for the Order 123

Thanks,

Sindhu

• ###### Re: Issue in calculating Turn for multiple dates

Please post a qlikview document that demonstrates the problem.

• ###### Re: Issue in calculating Turn for multiple dates

Hi,

Here I attached the sample source data and QV app.

• ###### Re: Issue in calculating Turn for multiple dates

Can anyone help me out in this...

• ###### Re: Issue in calculating Turn for multiple dates

Create a straight table, not a text box, and enter an expression along the lines as the ones above, Sum(End - Start)

• ###### Re: Issue in calculating Turn for multiple dates

Hi Michael,

But that is not working, however I need the turn for every iteration it travels that phases.

--Sindhu.

• ###### Re: Issue in calculating Turn for multiple dates

Try something like this: Sum({1<[OrderNumber]=>} [End-Start]))

• ###### Re: Issue in calculating Turn for multiple dates

What you want is not possible. There is not enough information to determine which startdate belongs to which enddate.

• ###### Re: Issue in calculating Turn for multiple dates

Exactly, when I load it it is doing n*n mappping.

But the right combinations are the ones which I posted in the Sample data.

I want to align it in this way and find the turn for it.

So I wanted some logic in making this work and avoid cross joins.

• ###### Re: Issue in calculating Turn for multiple dates

It is not possible. You need to come up with some sort of a unique key between the two tables other wise you end up having a cross join

• ###### Re: Issue in calculating Turn for multiple dates

You must define the logic to do the mapping. Qlikview cannot. And neither can I. I don't know which records should map to which record. I have no information that allows me to determine the logic to do that.

• ###### Re: Issue in calculating Turn for multiple dates

I tried generating a unique key with Order&Start&End combination, and when I join these dates but again ending up with cross joins.

If I have to create a logic with what I need to proceed.

Tried multiple scenarios, but failed.

Kindly help me in this scenario.

• ###### Re: Issue in calculating Turn for multiple dates

Hi Sindhu, the document attached returns the expected result but it has a very weak logic.

The first StartDate of an order is related to the first EndDate of that order, same for the 2nd, 3rd... For this sample of data is ok, but in real data I think you can find some issues.

• ###### Re: Issue in calculating Turn for multiple dates

I forgot, according to the example in your first post, the excel you provided has the end times in the Start sheet and the start times in the End sheet.

• ###### Re: Issue in calculating Turn for multiple dates

Hi Ruben,

Yes, the data I provided in excel is altered, actually the Start Sheet has the end dates and the End Sheet has the start dates.

But what I posted in the sample data in my first post is correct.

Trying to use your logic and check.

Many thanks,

Sindhu.

• ###### Re: Issue in calculating Turn for multiple dates

Hi Ruben,

Your logic worked perfectly

Fortunately didn't find any issues.

Many Thanks.

The method what you suggested is similar to what I used, but only the using of peek is different. I used it in another way, but didn't give proper results.

Can you please help in understanding this statement, how does it work.

If(Peek(Order)=Order, Peek(Cont)+1, 1) as Cont

It will help me in understanding more technically.

Thanks a lot...

Regards,

Sindhu

• ###### Re: Issue in calculating Turn for multiple dates

Hi Sindhu, as you don't know wich start is related to wich end I load them in sorted by : ORDER, DateTime (Start or End).

Using that sort, if the previous loaded Order is different from the current record it means is a new order, so the Cont starts at 1 (it goes for the 'else' part of the 'if).

If the Order is equal than the previous it takes the value Cont has in previous record and adds '1'.

This way Cont is used as the ID that relate each Start with his End: Start of Order x with Cont=1 will be related with the End of the same order and the same Cont.

Just to advise again that is a very weak logic, I don't know where the data came from but for example: there will be a Start, but if there is no End (because of a blackout, deleted record, duplicated start...), the next check for that order is again a Start...something like this will break all the logic.