12 Replies Latest reply on Jun 7, 2018 7:31 AM by Marvin Aliaga

# LOD fixing dimensions

Hi

I'm struggling with a problem, that some of you might know the answer to

The rows in my data are trips between cities with a vehicle id. The trips have no unique id. I  need to calculate the first (calc 1A) and the latest city(calc 1B) of these trips for each vehicle to each row. Using "fixed", "max/min" and "datediff" I can identify rows(trips) with the first and latest city for each vehicle, but I can't figure out how to "fixe" the first city and latest city dimension to all rows with the same vehicle ID.

secondly I need to calculate a trip numer/order based on the dates, so that I can see filter on fx the cehicles 3rd trip.

Wim Kegels you've been brilliant before - you might have an idea

• ###### 1. Re: LOD fixing dimensions

See attached!

• ###### 2. Re: LOD fixing dimensions

Hi,

You can create combined field using first city and latest city and vehicle id and then use it in fixed function.

• ###### 3. Re: LOD fixing dimensions

Below is snapshot of the result

Hope this could help

ZZ

1 of 1 people found this helpful
• ###### 4. Re: LOD fixing dimensions

Hi

in the attached

The calculation for the first date is           { FIXED [Calc 1A: First city],[Calc 1B: Latest City],[Vehicle ID]:Min([Date])}

The calculation for the last date is { FIXED [Calc 1A: First city],[Calc 1B: Latest City],[Vehicle ID]:Max([Date])}

Then add and Index() to the viz

Restarting every Veh ID

The the third trip calculation is also a table calculation           (if INDEX()=3 then min([Date])  end)  (note - I select the min date could have been any of the other dimensions)

with the following

and the viz will return this

Is this what you wanted?

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 5. Re: LOD fixing dimensions

Thanks. That fields with calc 1A calc 1b and Calc 2 in the data are the ones that I need to calculate.

As far as I know, Index is calculated in worksheet tables, and only exist there?  I need to be able to do calculations with number of trips, so I'm looking for a formula that creates the tripnumber as a datasource calculation/measure. Sorry if this wasn't clear in my intial text.

• ###### 6. Re: LOD fixing dimensions

Hi Marc-Anthony Di Biase thanks for "first" and "latest city" - your calculations was what I looked for. Do you know of the index tripnr problem I just mentioned above?

• ###### 7. Re: LOD fixing dimensions

Did you see my solution above with first and last city as well as the index of trip? is that what you are after?

ZZ

• ###### 8. Re: LOD fixing dimensions

Hi ZZ. Sorry. Both you and Marc had found the solution to the problem on first and latest cities. Thanks!. Do you know how to calculate the rank or the number of each trip in the datasource calculations and not as an index calculated in tables?

• ###### 9. Re: LOD fixing dimensions

The first thing I looked into was the Index calculation, however the index order is based on the order in which the data appears in the cross tab. When I re-organized my data such that the dates were in alphabetical order, the indexes did not reflect the actual order in the trips, but maintained the order of the data.

The solution to this problem was a rank calculated field, seen below;

Once this calculation was on the rows shelf, it is set to reset every vehicle ID;

The output is as follows, and is filterable based on the third trip;

See workbook attached!

1 of 1 people found this helpful
• ###### 10. Re: LOD fixing dimensions

Hi Marc-Anthony. This seems to be the best solution Thanks

• ###### 11. Re: LOD fixing dimensions

If you don't want use table calculation at all, here is a sample by using LOD to achieve

Workbook attached.

ZZ

1 of 1 people found this helpful
• ###### 12. Re: LOD fixing dimensions

Hi,

A couple of question:

1. How did you sort by Vehicle ID and date?

2. How did you created the trip/number order field?