
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Calculate number of products by time unit having transaction times
Hello the community !
I have a table in qlikview with two field: one containing the time (hh:mm) of transaction and another one containing the number of products. But here is the problem:
The number of products of the time T is in fact the number of products produced betwen T-1 and T.
What I want is to calculate the the number of products by time unit. For instance:
Time | Number of Products | Products by time unit
05:30 45 -
06:00 30 1=30prod/30min
Thank you !
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Assuming your Time field contains a proper QV time value (numerics), then in a straight table:
Dimension: Time
Column 1: Sum(Products)
Column 2: Sum(Products) / (Time - Above(Time))
This won't total properly. On the expression tab, select "Average of rows" as the total method, or use this for column 2:
Avg(Aggr(Sum(Products) / (Time - Above(Time)), Time))


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Assuming your Time field contains a proper QV time value (numerics), then in a straight table:
Dimension: Time
Column 1: Sum(Products)
Column 2: Sum(Products) / (Time - Above(Time))
This won't total properly. On the expression tab, select "Average of rows" as the total method, or use this for column 2:
Avg(Aggr(Sum(Products) / (Time - Above(Time)), Time))


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You might want to look into inter records functions, like peek() or previous().
Something like this:
LOAD
Time,
Number,
Number / (interval#(Time,'hh:mm')- interval#(peek(Time),'hh:mm') ) as ProductsPerDay
FROM TABLE;
It's calculating products per day, if you want a different base, multiply accordingly.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Is it possible (since I have for one time many lines) to express it in a chart this way:
Value(Dimension=T)/(Dimension(T)-Dimension(T-1))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this script:
Table:
LOAD *,
Num(Num#(Time(Time#(Time, 'hh:mm') - Time#(Peek('Time'), 'hh:mm'), 'hhmm'), '00##')) as Difference,
[Number of Products]/Num(Num#(Time(Time#(Time, 'hh:mm') - Time#(Peek('Time'), 'hh:mm'), 'hhmm'), '00##')) as [Products by time unit];
LOAD * Inline [
Time, Number of Products
05:30, 45
06:00, 30
];
Output:


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am sorry, but I do not understand your question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
In a bar chart where the dimension is Time, to use an expression where for Time the expression is:
Sum(Products)/ (Time - Previous Time)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yasser BENAZZOUZ wrote:
In a bar chart where the dimension is Time, to use an expression where for Time the expression is:
Sum(Products)/ (Time - Previous Time)
That's exactly what my expression does. Just create a bar chart instead of a straight table.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you, it actually works for me
