Calculate number of products by time unit having transaction times

[#1]

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 !

[jonathandienst · Accepted solution]

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))

[swuehl]

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.

[#4]

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))

[sunny_talwar]

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:

[jonathandienst]

I am sorry, but I do not understand your question.

[#7]

In a bar chart where the dimension is Time, to use an expression where for Time the expression is:

Sum(Products)/ (Time - Previous Time)

[jonathandienst]

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.

[#9]

Thank you, it actually works for me