topic Re: Qlikview Dynamic Table Doubt in QlikView

Qlikview Dynamic Table Doubt

katherine_mondr — Sun, 02 Aug 2015 23:12:47 GMT

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.

Re: Qlikview Dynamic Table Doubt

Mon, 03 Aug 2015 16:30:38 GMT

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

Re: Qlikview Dynamic Table Doubt

katherine_mondr — Mon, 03 Aug 2015 17:55:52 GMT

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!

Re: Qlikview Dynamic Table Doubt

Mon, 03 Aug 2015 18:15:00 GMT

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.

Re: Qlikview Dynamic Table Doubt

Mon, 03 Aug 2015 18:16:53 GMT

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

Re: Qlikview Dynamic Table Doubt

katherine_mondr — Mon, 03 Aug 2015 18:28:58 GMT

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?

Re: Qlikview Dynamic Table Doubt

Mon, 03 Aug 2015 18:50:49 GMT

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

Re: Qlikview Dynamic Table Doubt

katherine_mondr — Mon, 03 Aug 2015 18:56:17 GMT

Thanks lot Adam! I will try it!