Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
katherine_mondr
Partner - Contributor
Partner - Contributor

Qlikview Dynamic Table Doubt

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.

UnitDate/HourKm traveled
12/8/2015    10:10 am237
12/8/2015     8:15 pm543
13/8/2015     3:12 pm678
13/8/2015     9:00 pm908

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.


1 Solution

Accepted Solutions
Not applicable

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

View solution in original post

7 Replies
Not applicable

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
Partner - Contributor
Partner - Contributor
Author

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!

Not applicable

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.

Not applicable

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

katherine_mondr
Partner - Contributor
Partner - Contributor
Author

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?

Not applicable

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
Partner - Contributor
Partner - Contributor
Author

Thanks lot Adam! I will try it!