RangeSum Chart with Calculated Dimensions?

[#1]

Hi All,

I am trying to find some information of how to display a table with a RangeSum expression and a calculated dimensions in a chart.  I've searched the community pages and on twitter with no luck so far.

The table shows a range sum expression calculating the running average value per group by the calculated dimension of Week and Group.  (variables altered for privacy) The table shows the correctly calculated data however, when I convert this into a chart, the values are not displayed correctly - see screenshots and detail below.

Expression

=RangeSum(Above(TOTAL sum({<= {'l''}>}[value]),0,RowNo(TOTAL)))
/
RangeSum(Above(TOTAL Count(DISTINCT(C)),0,RowNo(TOTAL)))

Dimensions

Weeks

=if(([Creation Date]-[Date])/7<0,0,
if(isnull([Creation Date]-[Date]),0,Round(([Creation Date]-[Date])/7)))

Group

=if(Match(Group,'1','2','3'),Group)

Chart shows correct weeks and groups but the expression values are not in line with the correct values that are shown in the table - see axes from £0 - £18,000 - Summing the values rather than the rangesum.

Any help on this would be greatly appreciated

Thanks all!

Rachel

[sunny_talwar]

Try this as your expression:

Aggr(

     RangeSum(Above(Sum({<= {'l''}>}[value]),0,RowNo()))
     /
     RangeSum(Above(Count(DISTINCT(C)),0,RowNo()))

, Group, Weeks)

[#3]

Hi,

Thanks for getting back.

'Weeks' is a calculated dimension and not a variable so not recognised in the expression.

Rachel

[sunny_talwar]

May be create it in the script? Something like this

LOAD ([Creation Date]-[Date])/7 as NewField

and then it might work?

[#5]

Hi,

I had tried this previously without the Agg() and had no luck.

I have updated the script to include 'Weeks' for the dimension and the Agg() in the expression - see below table, values are not showing as rangesum

R

[#6]

Chart also not showing rangesum value - still summing

[sunny_talwar]

Would you be able to share a sample qvw file to look at the issue?

[#8]

I have attached an example qvd.

[sunny_talwar]

Is this sort of kind of what you are looking for?

Few things to note:

1) Select 'No Accumulation' instead of 'Full Accumulation'

2) Use set analysis instead of calculated dimension for Group

3) I don't know if you have QV12 or not, but it seems that Week are not sorted from 0 to n in ascending order, but if you have QV12 installed you can do it on the front end of the application using The sortable Aggr function is finally here!

Expression:

Aggr(

    RangeSum(Above(Sum({<Group = {1,2,3}>}Value),0,RowNo()))

/

RangeSum(Above(Count({<Group = {1,2,3}>} DISTINCT Concat),0,RowNo()))

, Group, (Weeks,(NUMERIC)))

[#10]

Hi Sunny,

Its definitely closer than I was!  See attached example - the 'No Accumulation' shows the correct direction of development with the curve, however,

• the set analysis for group does not work in the expression - one line shown and no individual groups shown

In the attached I have updated the expression to 'no accumulation' but the values in the chart still don't match the table.

Group 3 at 28 weeks in the table is £4,064 however the chart shows £4,056.

I think the dimension is causing the problem as when I click the groups in isolation the correct values are displayed in the chart!

Let me know if you have any ideas on this.

Thanks again

Rachel