Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
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))
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))
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.
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))
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:
I am sorry, but I do not understand your question.
In a bar chart where the dimension is Time, to use an expression where for Time the expression is:
Sum(Products)/ (Time - Previous Time)
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.
Thank you, it actually works for me