Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

RangeSum Chart with Calculated Dimensions?

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

14 Replies
Highlighted

Try this as your expression:

Aggr(


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


, Group, Weeks)

Highlighted
Not applicable

Hi,

Thanks for getting back.

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

Rachel

Highlighted

May be create it in the script? Something like this

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

and then it might work?

Highlighted
Not applicable

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

Highlighted
Not applicable

Chart also not showing rangesum value - still summing

Highlighted

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

Highlighted
Not applicable

I have attached an example qvd.

Highlighted

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

Capture.PNG

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

Highlighted
Not applicable

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