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: 
sifatnabil
Specialist
Specialist

3 month moving/rolling average in text box?

Is this possible? Usually the rolling averages are shown inside graphs where dimension is defined.

rangeavg(above(sum(Sales),0,3) - how could I define it such that it shows the 3 month average of the current month or week inside a textbox? Thanks.

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this if you have datefield in your app

=(Sum({<MonthDimensionName=, QuarterDimensionName=, YearDimensionName=, DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName), -2))<=$(=MonthEnd(Max(DateDimensionName)))'}>}Sales))/3


Note: MonthDimensionName, QuarterDimensionName, YearDimensionName and DateDimensionName with your actual field names.


Hope this helps you.


Regards,

Jagan.

View solution in original post

3 Replies
sifatnabil
Specialist
Specialist
Author

bump.

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this if you have datefield in your app

=(Sum({<MonthDimensionName=, QuarterDimensionName=, YearDimensionName=, DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName), -2))<=$(=MonthEnd(Max(DateDimensionName)))'}>}Sales))/3


Note: MonthDimensionName, QuarterDimensionName, YearDimensionName and DateDimensionName with your actual field names.


Hope this helps you.


Regards,

Jagan.

JonnyPoole
Employee
Employee

Here is a sample using a Date Calendar . I use a month index  1...n  to define the months in sequential order from oldest to most recent month in the data.  The set analysis is then very straightforward.  For a 3 month average:

=sum( {$<MonthIndex = {'>$(=vMaxMonthIndex-3)'}>}  Sales)  / 3