RangeSum with 2 Dimensions

[qliksensedlin]

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

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.

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.

[sunny_talwar]

Try this with Month instead of Submit Date

=Aggr(

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

[pablolabbe]

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]

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)

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

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 · Accepted solution]

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]

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

Thanks a lot for your good advise.