Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Get the last value from a rolling average

Hi

I currently have a line chart with the year/month as the dimension and the following rolling average expression plotted.

=rangeavg(above((kpi_1a.company_result),0,12))

I have now been ask to plot the latest months, rolling average value, therefor the "last" value from the above expression on a gauge chart. I'vebeen playing around with max() statements and calculated dimensions and if's inthe expression but as of yet I’ve had no joy.

I'd appreciate any help you people might be able to offer.

Cheers

Chris

3 Replies
swuehl
MVP
MVP

Hi Chris,

try something like

=max(aggr(rangeavg(bottom((kpi_1a.company_result),1,12)),YearMonth))

So you introduce a aggregation table using the advanced aggregation function aggr(), then use bottom to get the last 12 column segment values and feed them into rangeavg. You should get the same average value for all YearMonth values of your dimension in this embedded advanced aggregation table, so I just pick one with max().

Hope this helps,

Stefan

Not applicable
Author

First many thanks for your reply.

When I only select 12 months worth of data the maths behind the scenes seems to work with your expression.

However to get a true rolling average I'm selecting 24 months data as in the 1st item of my 12 months is an average of the previous 12 month's averages etc.

And when I select the full 24 periods it puts the single value calculated by the expression as incorrect (93% instead of 91.5% not that it really matters, I image it's down to the aggrigation which i havn't used before so not quite clear on what it is doing)

After reading through the reference manual i was wondering if i could use the firstsortedvalue() funtion ? However i don't have any experience of this it seems it should fit my needs, i havnt got the syntax right yet though. Do you think this could be a viable solution ?

swuehl
MVP
MVP

Hi Chris,

I haven't fully understood when you say 'as in the 1st item of my 12 months is an average of the previous 12 month's averages'.

Does your expression in your original post give you the correct numbers? How many dimensions do you use? If you use more than one dimension, like Year and Month instead of YearMonth, I imagine you get problems with the column segments and probably need to add a total qualifier to the bottom function.

I would be cautious to imply that the difference in the results is due to the aggregation, maybe, but probably not. So let's take a close look.

Could you post a simplified sample app here? Upload is available in advanced editor. If not, could you give some more details or post at least a small inline table with sample data here?

Regards,

Stefan

P.S: Regarding the firstsortedvalue() function, yes, maybe you could use it, but also not sure how. What is your idea, could you explain it in words what you want to do?

And, you could probably replace the aggr() and the bottom function with a set expression, something like this:

=avg( {<YearMonth={">=$(=max(YearMonth)-11)<=$(=max(YearMonth))"}>}  kpi_1a.company_result)