Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
yvonne-c
Contributor

Rolling average, 3 period average

I need to create the figures in the bottom table as KPIs - just struggling with the expression for the 3 batch average - it works in my main table but I can't get it to work alone

Can anybody help with this?

Thanks

DATE INAvg(DLWG1)Avg(aggr(rangeavg(above(Total Avg({<[DATE IN]=>}DLWG1),0,3)),[DATE IN]))
20/12/20181.021.02646717
10/01/20191.061.033968929
24/01/20191.131.070667567
07/02/20191.251.146228954
21/02/20191.271.216261005

 

Latest Batch3 Batch Avg
1.271.22
Labels (3)
1 Solution

Accepted Solutions

Re: Rolling average, 3 period average

Perhaps this?

1. Max({<[DATE IN] = {"$(=Max([DATE IN]))"}>} Aggr(Avg(DLWG1), [DATE IN]))

2. Avg({<[DATE IN] = {"$(=Max([DATE IN]))"}>} aggr(rangeavg(above(Total Avg({<[DATE IN]=>}DLWG1),0,3)),[DATE IN]))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

3 Replies

Re: Rolling average, 3 period average

Perhaps this?

1. Max({<[DATE IN] = {"$(=Max([DATE IN]))"}>} Aggr(Avg(DLWG1), [DATE IN]))

2. Avg({<[DATE IN] = {"$(=Max([DATE IN]))"}>} aggr(rangeavg(above(Total Avg({<[DATE IN]=>}DLWG1),0,3)),[DATE IN]))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

yvonne-c
Contributor

Re: Rolling average, 3 period average

Number 2 works great!

Thankyou

Re: Rolling average, 3 period average

Glad you like my second expression, But can you simplify as this for first one?

Avg({<[DATE IN] = {"$(=Max([DATE IN]))"}>} DLWG1)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)