-
Re: RangeSum with 2 Dimensions
Sunny Talwar Feb 27, 2018 12:42 PM (in response to Di Lin)Try this with Month instead of Submit Date
=Aggr(
RangeSum(Above(Count({<[NPS Category] = {'Detractor'}>}Score), 0, RowNo()))
, Division, Month)-
Re: RangeSum with 2 Dimensions
Di Lin Feb 27, 2018 7:52 PM (in response to Sunny Talwar )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?
-
Re: RangeSum with 2 Dimensions
Sunny Talwar Feb 27, 2018 9:55 PM (in response to Di Lin)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)))
-
Re: RangeSum with 2 Dimensions
Di Lin Feb 27, 2018 10:28 PM (in response to Sunny Talwar )Yes! NUMERIC does the trick. I guess the loading sequence matters for this RangeSum(Above) calculation.
Thanks a lot for your good advise.
-
-
-
-
Re: RangeSum with 2 Dimensions
Pablo Labbe Feb 27, 2018 2:25 PM (in response to Di Lin)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