Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

qliksensedlin
New Contributor II

RangeSum with 2 Dimensions

Hi,

I have a problem with using combination of RangeSum and Above functions to get a cumulative score by month.

Here is how my data looks like. I need to accumulate the score by "Month" column. But as I also have a second column "Division", my accumulation resets every 2 rows. The expression I'm using for the "Cumulative Detractor Count" column is:

=rangesum(above(Count({<[NPS Category] = {'Detractor'}>}Score),0,rowno()))

1.PNG

I know this has to do with the order of the columns. And if I switch the "Month" and "Division" columns, I can get the correct result in "Cumulative Detractor Count" column as below.

2.PNG

Unfortunately, my table has to be in the "Month" -> "Division" order (as this in effect will become a line chart having "Month" as the first dimension and "Division" as the second dimension).

I tried to improve my expression by adding Aggr function and reset the column order logic. Here is what I have:

=aggr(

rangesum(above(Count({<[NPS Category] = {'Detractor'}>}Score),0,rowno()))
,Division, [Submit Date]
)

However, the result as shown in "Cumulative Detractor Count 2" column contains no data at all.

I tried to searched multiple treads online, but couldn't figure out what is missing in my expression.

Could someone please help me get this expression working right?

Thank you in advance.

1 Solution

Accepted Solutions
MVP
MVP

Re: RangeSum with 2 Dimensions

You have been right about all the issues you brought up. In order to address them, all you need to do is to create the Month field in the script like this

LOAD [Submit Date],

    MonthName([Submit Date]) as [Submit Month]

FROM...;

and then try this

=Aggr(

RangeSum(Above(Count({<[NPS Category] = {'Detractor'}>}Score), 0, RowNo()))

, Division, [Submit Month])

If this doesn't work, you can try this

=Aggr(

RangeSum(Above(Count({<[NPS Category] = {'Detractor'}>}Score), 0, RowNo()))

, Division, ([Submit Month], (NUMERIC)))

5 Replies
MVP
MVP

Re: RangeSum with 2 Dimensions

Try this with Month instead of Submit Date

=Aggr(

RangeSum(Above(Count({<[NPS Category] = {'Detractor'}>}Score), 0, RowNo()))
, Division, Month)

pablolabbe
Valued Contributor II

Re: RangeSum with 2 Dimensions

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others

qliksensedlin
New Contributor II

Re: RangeSum with 2 Dimensions

Hi Sunny,

Thanks for your reply. I tested it out, but it didn't work.

Here is the output with your suggesion:

=Aggr(

RangeSum(Above(Count({<[NPS Category] = {'Detractor'}>}Score), 0, RowNo()))
, Division, Month)

3.PNG

It is probabaly because "Month" is not an intrinsic dimension in my table. It is created using the expression MonthName([Submit Date]). I guess Aggr() does not accept MonthName([Submit Date]) as its parameter.


Even I tried to switch to [Submit Date] as the dimension directly, using following to aggregate, it still does not give me the correct output.


=Aggr(

RangeSum(Above(Count({<[NPS Category] = {'Detractor'}>}Score), 0, RowNo()))

, Division, [Submit Date])


4.PNG


So the problem is

1st, I need to get the aggregation right for table with 2 dimensions.

2nd, I need the aggregation to be done on a dimension with expression MonthName ([Submit Date]).

Any suggestions?

MVP
MVP

Re: RangeSum with 2 Dimensions

You have been right about all the issues you brought up. In order to address them, all you need to do is to create the Month field in the script like this

LOAD [Submit Date],

    MonthName([Submit Date]) as [Submit Month]

FROM...;

and then try this

=Aggr(

RangeSum(Above(Count({<[NPS Category] = {'Detractor'}>}Score), 0, RowNo()))

, Division, [Submit Month])

If this doesn't work, you can try this

=Aggr(

RangeSum(Above(Count({<[NPS Category] = {'Detractor'}>}Score), 0, RowNo()))

, Division, ([Submit Month], (NUMERIC)))

qliksensedlin
New Contributor II

Re: RangeSum with 2 Dimensions

Yes! NUMERIC does the trick. I guess the loading sequence matters for this RangeSum(Above) calculation.

Thanks a lot for your good advise.