5 Replies Latest reply: Feb 27, 2018 10:28 PM by Di Lin RSS

    RangeSum with 2 Dimensions

    Di Lin
    Di Lin Feb 26, 2018 10:18 PM

      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.

        • Re: RangeSum with 2 Dimensions
          Sunny Talwar
          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
              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)

               

              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?

                • Re: RangeSum with 2 Dimensions
                  Sunny Talwar
                  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
                Pablo Labbe
                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