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.

[qliksensedlin]

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.

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

Thank you.

[sunny_talwar]

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