Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qliksensedlin
Contributor II
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.

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

5 Replies
sunny_talwar

Try this with Month instead of Submit Date

=Aggr(

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

pablolabbe
Luminary Alumni
Luminary Alumni

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
Contributor II
Contributor II
Author

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?

sunny_talwar

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
Contributor II
Contributor II
Author

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

Thanks a lot for your good advise.