Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL:
**Learn More**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Qlikview Dynamic Table Doubt

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Showing results for

katherine_mondr

Partner - Contributor

2015-08-02
07:12 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

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.

1,044 Views

1 Solution

Accepted Solutions

Not applicable

2015-08-03
02:50 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

896 Views

7 Replies

Not applicable

2015-08-03
12:30 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

896 Views

katherine_mondr

Partner - Contributor

2015-08-03
01:55 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!

896 Views

Not applicable

2015-08-03
02:15 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

896 Views

Not applicable

2015-08-03
02:16 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

896 Views

katherine_mondr

Partner - Contributor

2015-08-03
02:28 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

896 Views

Not applicable

2015-08-03
02:50 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

897 Views

katherine_mondr

Partner - Contributor

2015-08-03
02:56 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks lot Adam! I will try it!

896 Views