topic Re: Cumulative Average Rating over Distinct Reviews by RangeSum and Aggr in App Development

Cumulative Average Rating over Distinct Reviews by RangeSum and Aggr

qliksensedlin — Wed, 22 Dec 2021 20:40:09 GMT

Hi,

I have a data set that contains product reviews and their respective ratings (1 to 5). I'd like to create a chart to display the cumulative average rating trend by each product.

Here is the expression I used to calculate the cumulative average rating:

RangeSum(Above(Sum(Agg(DISTINCT review_rating, review_id)), 0, RowNo()))

RangeSum(Above(Count(DISTINCT review_id), 0, RowNo()))

As I have the same reviews spanning across multiple rows, I need to take the distinct reviews with their ratings.

This expression works fine when I try it in a table. It calculates the correct cumulative average rating for different products for me. However, it is not working when I add it to my line chart. The same expression will just give me the monthly average rating instead. No cumulation happens.

My line chart has MonthName(review_date) as the Group and product_name as the bar.

Thanks in advance for any tips and help.

Re: Cumulative Average Rating over Distinct Reviews by RangeSum and Aggr

sunny_talwar — Mon, 22 Jul 2019 12:47:56 GMT

Would you be able to share a sample where we can see the issue?

Re: Cumulative Average Rating over Distinct Reviews by RangeSum and Aggr

qliksensedlin — Tue, 23 Jul 2019 02:55:24 GMT

Hi Sunny,

I have added the screenshot of table and chart here.

You can see that the last column in table calculates the cumulative review rating using the expression I mentioned. However, when the same expression is used in chart the results are different. It appears to calculate the monthly average rating instead as shown in the 5th column in my table.

Thank you.

Re: Cumulative Average Rating over Distinct Reviews by RangeSum and Aggr

sunny_talwar — Tue, 23 Jul 2019 11:21:12 GMT

First thing first, create a new field in the script for MonthName(review_date) as Review_MonthName.... this would make your life easy... next... try this expression

Aggr( RangeSum(Above(Sum(Agg(DISTINCT review_rating, review_id)), 0, RowNo())) / RangeSum(Above(Count(DISTINCT review_id), 0, RowNo())) , product, (Review_MonthName, (NUMERIC)))

Re: Cumulative Average Rating over Distinct Reviews by RangeSum and Aggr

qliksensedlin — Wed, 24 Jul 2019 05:09:05 GMT

Hi Sunny,

Many thanks for your help. This works like charm. Although I'm not so sure why an additional aggregation is required for the chart. Is it because 2 dimensions are involved?

I have a follow-up question. With the cumulative average rating calculated, I would like to keep the value regardless of the review month selected by user. I tried to add set modifier in following way, but it didn't work out as I expected.

Aggr( RangeSum(Above(Sum({<review_month=>} Agg(DISTINCT review_rating, review_id)), 0, RowNo())) / RangeSum(Above(Count({<review_month=>} DISTINCT review_id), 0, RowNo())) , product, (review_month, (NUMERIC)))

Any tips on how i can make it work?

Thank you.

Re: Cumulative Average Rating over Distinct Reviews by RangeSum and Aggr

sunny_talwar — Wed, 24 Jul 2019 11:12:51 GMT

You will need to ignore the selection in review_month on the outer aggregation as well... try this

Only({<review_month=>} Aggr( RangeSum(Above(Sum({<review_month=>} Agg(DISTINCT review_rating, review_id)), 0, RowNo())) / RangeSum(Above(Count({<review_month=>} DISTINCT review_id), 0, RowNo())) , product, (review_month, (NUMERIC))) )

Re: Cumulative Average Rating over Distinct Reviews by RangeSum and Aggr

qliksensedlin — Thu, 25 Jul 2019 02:21:02 GMT

Hi Sunny,

Thanks for the tips. I tried it, but failed...

Please refer to the two screenshots below. I have first unfiltered for the review_month, then filtered for 2019 reviews.

You can see that the cumulative count works, but the cumulative sum rating doesn't. It only adds up reviews from Jan 2019 to Jul 2019. As a result, the cumulative average rating is wrong too.

Here is my expression for cumulative count

RangeSum(Above(Count({<review_month=>}DISTINCT review_id), 0, RowNo()))

cumulative sum rating

Only({<review_month=>} Aggr( RangeSum(Above(SUM({<review_month=>} AGGR(DISTINCT review_rating,review_id)), 0, RowNo())), product, (review_month, (NUMERIC)) ) )

cumulative average rating

Only({<review_month=>} Aggr( RangeSum(Above(SUM({<review_month=>} AGGR(DISTINCT review_rating,review_id)), 0, RowNo())) / RangeSum(Above(COUNT({<review_month=>} DISTINCT review_id), 0, RowNo())), product, (review_month, (NUMERIC)) ) )

In addition, I see that the chart with "only" modifier does not change the x-axis range according to review_month selection. It's not critical, but it would be ideal if we can just visualize the selected review months.

Thank you.

Re: Cumulative Average Rating over Distinct Reviews by RangeSum and Aggr

sunny_talwar — Thu, 25 Jul 2019 11:30:33 GMT

One more change... adding another Only() within the Aggr() function....and removed DISTINCT

Only({<review_month>} Aggr( RangeSum(Above(Sum({<review_month>} Aggr(Only({<review_month>} review_rating), review_id)), 0, RowNo())), product, (review_month, (NUMERIC)) ) )

Re: Cumulative Average Rating over Distinct Reviews by RangeSum and Aggr

qliksensedlin — Fri, 26 Jul 2019 02:46:15 GMT

Hi Sunny ,

The calculation works perfect. However, when I select the period in a chart, it doesn't make any difference visually...

My average rating expression is now

=Only({<review_month>} Aggr( RangeSum(Above(SUM({<review_month=>} AGGR(Only({<review_month>} review_rating),review_id)), 0, RowNo())) / RangeSum(Above(COUNT({<review_month=>} DISTINCT review_id), 0, RowNo())), product, (review_month, (NUMERIC)) ) )

Thank you.

Re: Cumulative Average Rating over Distinct Reviews by RangeSum and Aggr

sunny_talwar — Fri, 26 Jul 2019 11:09:08 GMT

May be this if you intend to see only the review_month which are selected but still keep the accumulation...

=Aggr( RangeSum(Above(SUM({<review_month=>} AGGR(Only({<review_month>} review_rating),review_id)), 0, RowNo())) / RangeSum(Above(COUNT({<review_month=>} DISTINCT review_id), 0, RowNo())), product, (review_month, (NUMERIC)) )

Re: Cumulative Average Rating over Distinct Reviews by RangeSum and Aggr

qliksensedlin — Mon, 29 Jul 2019 02:16:24 GMT

Hi Sunny,

Thanks for your help. However, this will just result in a single line in the chart. No product specific line is available.

I think I will just accept the fact that accumulation could not work for months invisible in the chart and apply the previous expression you suggested.

Thank you.

Re: Cumulative Average Rating over Distinct Reviews by RangeSum and Aggr

sunny_talwar — Mon, 29 Jul 2019 11:23:01 GMT

I think I am running into an issue where I am not 100% sure what you are after. May be if you can share a sample where we can see the issue, I might be able to help better.

Best,
Sunny