Qlikview Dynamic Table Doubt

[katherine_mondr]

Hello everyone!

I am working on a project in which I need to calculate the total distance traveled for each unit that the company has and then put those values into a qlikview dynamic table. In order to calculate the total distance I need to substract the final value of kilometers traveled from the initial value of kilometers traveled. Each unit reports every certain time the amount of kilometers traveled so it appears something like the following table.

Unit	Date/Hour	Km traveled
1	2/8/2015    10:10 am	237
1	2/8/2015     8:15 pm	543
1	3/8/2015     3:12 pm	678
1	3/8/2015     9:00 pm	908

In qlikview I want to have a dynamic table where I can see:

     - The total amount of kilometers that a unit traveled in an specific day, an specific month, an specific year.

       In the case of the previous if I want to know how many kilometers did unit 1 traveled on 2/8/2015 I need to substract 543 - 237= 306        kilometers traveled.

I want to know what expression is needed to make those operations in qlikview.

[#2]

If you add the date as a dimension and create as a filter/selction criteria, you can then select the date you want.  In the example you provided, you would just select 2/8/2015 from your date filter - I am not sure why you are subtracting since both are unit 1 on 2/28/15.

In the load script you can get the specific year and specifc month by by using month (<date>) field as month, year(<datefield>) as year, - this would give you 2 more fields to use in your filter selection criteria

[katherine_mondr]

The substraction is because I want to know the kilometers traveled durind an specified day, month, year, etc. According to the table on day 2/8/2015 the unit has already traveled 237 km on previous days, so if the last register of kilometers is of 543 I need to substract 543-237. If I wanted to know the amount of kilometers traveled only on day 3/8/2015 I need to substract 908- 678 and so on.

Thanks for the answer!

[#4]

ahhh

I did not realize the mileage was cumulative

What about an expression to cacluate the mile each day - it would be the current row minus the row-1

so in the examples you gave me 543-237, the next row would be 678-543, then 908-678, etc.

[#5]

or based on how your data is defined, calculate the same basic logic in the load script, if possible

[katherine_mondr]

Sorry I forgot to explain that detail. I'm new using Qlikview how can I program row substractions on the Qlikview expression? Which commands can I use?

[#7 · Accepted solution]

create a new expression in your table - the expression would be

sum( [(KM Traveled)]) - above( sum( [KM Traveled]))

This will take the value is the column KM traveled for the current row and subtract the value in KM Traveled on the previous row and store that value in the new column

the KM Traveled is assuming that is the name of the field - if it is not, replace with the correct value

[katherine_mondr]

Thanks lot Adam! I will try it!