Cumulative Average Rating over Distinct Reviews by RangeSum and Aggr

[qliksensedlin]

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.

[sunny_talwar]

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

[qliksensedlin]

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.

[sunny_talwar · Accepted solution]

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)))

[qliksensedlin]

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.

[sunny_talwar]

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)))
)

[qliksensedlin]

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.

[sunny_talwar]

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))
)
)

[qliksensedlin]

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.

[sunny_talwar]

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))
)