topic Re: RangeSum Chart with Calculated Dimensions? in QlikView

RangeSum Chart with Calculated Dimensions?

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: RangeSum Chart with Calculated Dimensions?

sunny_talwar — Wed, 11 Jan 2017 17:21:24 GMT

Try this as your expression:

Aggr(

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

, Group, Weeks)

Re: RangeSum Chart with Calculated Dimensions?

Wed, 11 Jan 2017 20:17:35 GMT

Hi,

Thanks for getting back.

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

Rachel

Re: RangeSum Chart with Calculated Dimensions?

sunny_talwar — Wed, 11 Jan 2017 20:50:51 GMT

May be create it in the script? Something like this

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

and then it might work?

Re: RangeSum Chart with Calculated Dimensions?

Thu, 12 Jan 2017 10:03:34 GMT

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

Re: RangeSum Chart with Calculated Dimensions?

Thu, 12 Jan 2017 10:06:45 GMT

Chart also not showing rangesum value - still summing

Re: RangeSum Chart with Calculated Dimensions?

sunny_talwar — Thu, 12 Jan 2017 16:37:10 GMT

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

Re: RangeSum Chart with Calculated Dimensions?

Fri, 13 Jan 2017 12:15:12 GMT

I have attached an example qvd.

Re: RangeSum Chart with Calculated Dimensions?

sunny_talwar — Fri, 13 Jan 2017 12:24:47 GMT

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

Re: RangeSum Chart with Calculated Dimensions?

Fri, 13 Jan 2017 15:37:08 GMT

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

Re: RangeSum Chart with Calculated Dimensions?

sunny_talwar — Fri, 13 Jan 2017 15:50:49 GMT

You changed the expression I gave you . You will have to use this:

=Aggr(RangeSum(Above(sum(Value),0,RowNo()))

RangeSum(Above(Count(DISTINCT(Concat)),0,RowNo())), Group, (Weeks, (Numeric)))

Not going to work without Aggr() I am afraid

Re: RangeSum Chart with Calculated Dimensions?

Mon, 16 Jan 2017 09:58:28 GMT

Thanks! could you please include the qvw?

I have 'no data to display' in the chart with the AGGR() expression.

Re: RangeSum Chart with Calculated Dimensions?

sunny_talwar — Tue, 17 Jan 2017 01:59:40 GMT

If you open this link, you will see the qvw attached with my previous response

Re: RangeSum Chart with Calculated Dimensions?

Tue, 17 Jan 2017 09:24:35 GMT

Thanks - your attached qvw is the same though - 'no data to display'

Re: RangeSum Chart with Calculated Dimensions?

sunny_talwar — Tue, 17 Jan 2017 10:22:41 GMT

I have asked this (mentioned it) couple of times about the version of QlikView you have. I think you don't have QV12 and my solution will only work in QV12. For anything below QV12, you will have to fix the order of Week field in the script to make this to work.

New Script

Temp:

LOAD RowNo() - 1 as Weeks

AutoGenerate 10000;

LOAD Concat,

Group,

Type,

[Creation Date],

Date,

Value,

FN,

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

FROM

[example rangesum.xlsx]

(ooxml, embedded labels, table is Sheet1);

DROP Table Temp;

Make sure the Temp table create enough Weeks to cover your Weeks from your main table calculation. I have created 10,000 weeks in ascending order just so that we can use the load order from Aggr() function. Later in the script, I drop the temp table.

New expression:

=Aggr(RangeSum(Above(sum(Value),0,RowNo()))

RangeSum(Above(Count(DISTINCT(Concat)),0,RowNo())), Group, Weeks)

I am attaching the qvw with my response. Please check it out.

Best,

Sunny