Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Contributor III

RangeAvg to be calculated for a column based on a range determined by a value in another column

I have a Shipment Count chart table consisting of the dimensions [Day Month], [year] and the expression [Count] which is a count of individual shipments for each [Day Month] (eg. "25 Feb"") and [year] (specifically 2019 and then 2020). I have just added a new expression "RangeAvg (Above(Count,0,RowNo()))" which does a RangeAvg for all the values in the [Count] field. For the first [Count] field value (top row), the RangeAvg will equal the [Count] field value, but starting with the second [Count] field, the RangeAvg does an average of that field and all the ones above it.

Here is my question: Since my data runs across 2 years (2019 and 2020) and does NOT include all months in each year (its only 18 Feb to 17 Mar for each year), I want the RangeAvg function to restart from zero once it crosses from a row with year=2019 to a row with year=2020. So in a sense I want to group my data by year with regard to RangeAvg.

How can I accomplish this?

Thanks

Jason

Labels (1)
• rangeavg

1 Solution

Accepted Solutions
MVP

Seems like it is already resetting, isn't it?

If it was not resetting, you would have seen a different number for 18Feb and not 106 which is equal to the count of 106, right?

2 Replies
MVP

Seems like it is already resetting, isn't it?

If it was not resetting, you would have seen a different number for 18Feb and not 106 which is equal to the count of 106, right?

Contributor III
Author

Thanks for pointing that out! I did just see it myself and was about to post.

Community Browser