moving average of items on time dimension

[#1]

Hi everybody,

my task is to create a Line-Chart where an sliding average is displayed over a time axis.

The time axis should display an "night working shift" which starts at 22:20h and stops the next day day 06:20h.

Each item has a EVENT-Time "minute" which has precision in minutes. But there might be several items within the same minute.

Each item has a unique "ID" (a 10-digit number) which also represents the sequence of the items.

Each item has a "quality" [good/bad]

What I have to do is the following:

Find the good items within the last 20 items (regarding used filters so I can' t do it at load time) and divide the result by 20 and display it on a time X-Axis.

My current solution:

As I want to calculate the fraction of the last 20 good items to all last 20 items I am trying it with a rangecount expression where EVERY item occurs:

rangesum(above(count({<QUALITY = {'good'}>}ID),1,20))
/
rangesum(above(count(ID),1,20))

This works fine. The chart is correct.

BUT what I need is the Timeline on the x-Axis....

Is there any help?

Thx in advance!

Martin

[johnw]

I think you're almost there, but I think you need a new timestamp field. Your event time is only accurate to the minute, but your ID indicates sequence within the minute. That sequence is important when you want the average of the last 20 items, so I think you need to map these IDs to separate timestamps (to indicate sequence) instead of to the same timestamp. For instance, create this:

timestamp(rangesum(EVENT_TIME,if(EVENT_TIME=previous(EVENT_TIME),time#('00:00:00.001','hh:mm:ss.fff'))),'YYYY-MM-DD hh:mm:ss.fff') as EVENT_TIME2

And wouldn't you also want the current ID counted at its own timestamp? If so, I think you want above(...,0,20) instead of above(...,1,20). So this:

rangesum(above(count({<QUALITY={'good'}>}ID),0,20))
/rangesum(above(count(ID),0,20))

And finally, use EVENT_TIMESTAMP2 as your dimension, and set the X axis to continuous. That seems to produce the graph that I think you want.

[#3]

Hi John,

yes that is the way I thought I'd have to do it. As there might be several items within on minute I did the following, which works fine, but what if there are more than 5 items in one minute? Is there a recursive chance to solve that?

An another question: Changing the Format Pattern of time for the conituous axis to 'HH:mm' does not take any effect. I'm working on 9.0 SR3. Is it a bug?

MyData:
Load
ID,
EVENT_TIME,
QUALITY,
if (EVENT_TIME = previous(EVENT_TIME),
if (EVENT_TIME = previous(previous(EVENT_TIME)),
if (EVENT_TIME = previous(previous(previous(EVENT_TIME))),
if (EVENT_TIME = previous(previous(previous(EVENT_TIME))),
if (EVENT_TIME = previous(previous(previous(EVENT_TIME))),
timestamp(EVENT_TIME & ':05', 'YYYY-MM-DD hh:mm:ss'),
timestamp(EVENT_TIME & ':04', 'YYYY-MM-DD hh:mm:ss')
),
timestamp(EVENT_TIME & ':03', 'YYYY-MM-DD hh:mm:ss')
),
timestamp(EVENT_TIME & ':02', 'YYYY-MM-DD hh:mm:ss')
),
timestamp(EVENT_TIME & ':01', 'YYYY-MM-DD hh:mm:ss')
),
timestamp(EVENT_TIME & ':00', 'YYYY-MM-DD hh:mm:ss')
) as timeIdent

Resident BaseData
order by EVENT_TIME ASC, ID ASC;

[johnw]

martin.goettler wrote:I did the following, which works fine, but what if there are more than 5 items in one minute? Is there a recursive chance to solve that?

I already gave a solution, and it handles 60,000 events in the same minute instead of 5, unless you see a bug in it, and that's why you were trying to write your own version?

As for why it won't format the continuous X-axis like you tell it to, that I don't know. Seems strange. Yeah, might be a bug, but if so, it isn't fixed with SR4 either, because I couldn't get it to work.

[#5]

Sorry John,

I think I'm not good enough in QlikView to understand your suggestion correctly 😞

I really appreciate your help - thx a lot!!

Using Your suggestion in the script

timestamp(rangesum(EVENT_TIME,if(EVENT_TIME=previous(EVENT_TIME),time#('00:00:00.001','hh:mm:ss.fff'))),'YYYY-MM-DD hh:mm:ss.fff') as EVENT_TIME2

creates the same millisecond for all the following items within one minute.

Let me try to explain how I understand your suggestion:

FIRST timestamp:

timestamp( //create a timestamp
rangesum(EVENT_TIME,if(EVENT_TIME=previous(EVENT_TIME),time#('00:00:00.001','hh:mm:ss.fff'))) //value of the timestamp to be created
,'YYYY-MM-DD hh:mm:ss.fff' //format of the timestampto be created
) as EVENT_TIME2

SECOND rangesum:

rangesum( // add the values within brackets
EVENT_TIME, // EVENT_TIME as summand one
if(EVENT_TIME=previous(EVENT_TIME),time#('00:00:00.001','hh:mm:ss.fff')) //one millisecond as summand two
)

Executing this in a script means adding one milisecond to a identical prevous EVENT_TIME.

Example: running through:

0,2010-06-09 03:45,good
1,2010-06-09 03:56,good
2,2010-06-09 03:56,good
3,2010-06-09 03:56,good
4,2010-06-09 03:56,good

Looking at 1: 2010-06-09 03:56 is NOT identical to previous 2010-06-09 03:45, RESULT is 2010-06-09 03:56 as timestamp = 2010-06-09 03:56:00.000

Looking at 2: 2010-06-09 03:56 IS identical to previous 2010-06-09 03:56, RESULT is 2010-06-09 03:56 + 00:00:00.001 as timestamp = 2010-06-09 03:56:00.001 GREAT!

Looking at 3: 2010-06-09 03:56 IS AGAIN identical to previous 2010-06-09 03:56, RESULT is 2010-06-09 03:56 + 00:00:00.001 as timestamp = 2010-06-09 03:56:00.001 BAD. It should be 2010-06-09 03:56:00.002, because it is the second identical match.

That's why I was asking fpr a recursive solution.

Did I miss anything in your post?

I can't see how 60.000 different values can be created????

Thx again!

Martin

[johnw · Accepted solution]

Oh, you're right, my suggestion WAS broken. Sorry about that.

Fortunately, it's easily fixed by incrementing from the timestamp we're creating instead of from the one we're reading in. The critical piece missing from the previous incorrect solution is underlined:

timestamp(if(EVENT_TIME=previous(EVENT_TIME)
,peek(timeIdent)+time#('00:00:00.001','hh:mm:ss.fff')
,EVENT_TIME),'YYYY-MM-DD hh:mm:ss.fff') as timeIdent

And this time I tested with four same EVENT_TIMEs instead of just with two. Appeared to work fine, though glancing at the results for five seconds isn't proof of a working algorithm.

[#7]

Thx John!

this solutions works perfectly!

Sorry about the delay but the project came up heavy 😞

Good job again - I do find many of your solutions in the forum very helpful!

Me as a newbie needs a trainer like you!

Martin