Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL:
**Learn More**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Rolling-n: Why does sum(aggr(rangeavg(above(total ...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

andreas_koehler

Creator II

2016-09-29
06:57 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Rolling-n: Why does sum(aggr(rangeavg(above(total sum..())))) not work with more than one dimension?

Hi all,

I need to calculate a variable rolling avg of sum() per months for a second dimension.

So I am using as formula

=sum(aggr(rangeavg(above(sum({$<Date=>}Value),0,3)),Date))

(here the variable is substituted by the fixed value of 3).

It works well in a straight table with [Date] as dimension but if fails in a straight table with another dimension.

Please have a look at the example. I resorted the table as ordered by Date asc first,then by the second dimension.

My questions are:

A) Why does it show the total in one row of the table "Selector as Dim" and the other row being empty?

B) How do I properly calculate the sum of rolling averages for each value of the dimension [Selector]?

Thanks for any help,

Andreas

2,980 Views

1 Solution

Accepted Solutions

sunny_talwar

MVP

2016-09-29
07:01 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May be you need this?

**=Sum(Aggr(RangeAvg(Above(TOTAL Sum(Value),0,3)), Selector, Date))**

4 Replies

sunny_talwar

MVP

2016-09-29
07:01 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May be you need this?

**=Sum(Aggr(RangeAvg(Above(TOTAL Sum(Value),0,3)), Selector, Date))**

vinieme12

Champion III

2016-09-29
07:06 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

You need to aggregate your expression on the dimension used

so for the below change Aggregation from [Date] dimension to [Selector]

=sum(aggr(rangeavg(above(sum({$<Date=>}Value),0,3)),Date))

to

=sum(aggr(rangeavg(above(sum({$<Date=>}Value),0,3)),Selector))

The expression total rownumber is 0; so rangeavg for rows above 0 is nothing! that is why it returns null. Change this to sum of rows.

Vineeth Pujari

If a post helps to resolve your issue, please accept it as a Solution.

If a post helps to resolve your issue, please accept it as a Solution.

andreas_koehler

Creator II

2016-09-29
07:24 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thank you Sunny,

I recall now that some time ago I posted a similar problem. Seems like I need a bigger hammer to get this into my head.

One question about the TOTAL: What is its result in a aggr()? I understand that it disregards the dimension in a chart.

**=Sum(Aggr(RangeAvg(Above(TOTAL Sum(Value),0,3)), Selector, Date))**

What dimension would be disregarded in this solution and what would be its purpose?

The correct result for B is in my understanding 19150 and not 19053.

I get 19150 as a result when I omit the TOTAL.

Andreas

2,254 Views

sunny_talwar

MVP

2016-09-29
07:27 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Well Total within Above function makes it a continuous RangeAvg when you have multiple dimensions (Selector and Date) where as without TOTAL it would be RangeAvg for your second dimension which resets for change in your first dimension (RangeAvg for Date which resets for each of the selector). Above cannot take field name after TOTAL.

Does that make sense?