2 Replies Latest reply: Sep 26, 2018 12:05 AM by Justin Pham RSS

    Ordered cumulative sum in Qlik Sense

    Luis García-Moreno

      Hello,

       

      I need to build a concentration curve in Qlik Sense.

       

      My source table looks as follows:

       

      Sales TypeCustomerSales
      ShopFrank25
      WebFrank50
      WebLucy250
      ShopAnna10
      ShopTom100
      WebTom100

       

      And I want to obtain something like:

      CustomerSalesCumulative SumRank
      Lucy2502501
      Tom2004502
      Frank755253
      Anna105354

       

      Ideally, it would also be great if I could represent the concentration curve in a line chart or scatter plot:

       

      CC.png

       

      Is this possible?

       

      Thanks!

        • Re: Ordered cumulative sum in Qlik Sense
          Quy Nguyen

          Try this below script:

          
          Temp:
          Load * INLINE [
          SalesChannel, TypeCustomer, Sales
          Shop, Frank, 25
          Web, Frank, 50
          Web ,Lucy, 250
          Shop, Anna, 10
          Shop, Tom, 100
          Web ,Tom ,100];
          
          
          AggrData:
          Load TypeCustomer, Sum(Sales) As Sales, Sum(Sales) As AccumSales
          Resident Temp
          Group by TypeCustomer;
          Drop Table Temp;
          
          
          NoConcatenate
          SortedData:
          Load TypeCustomer, Sales, AccumSales
          Resident AggrData
          Order by AccumSales desc;
          Drop Table AggrData;
          
          
          NoConcatenate
          FinalData:
          Load TypeCustomer, Sales, Alt(Peek(AccumSales),0) + Sales As AccumSales, RowNo() As Rank
          Resident SortedData;
          Drop Table SortedData;
          
          
          • Re: Ordered cumulative sum in Qlik Sense
            Justin Pham

            Hi Luis García-Moreno,

            You can try:

             

            I'm using Bar Chart

            Dimension: Customer

             

            Measure:

            rangesum( above( sum([Sales]),0,rowno()))
            

             

            Sorting by Expression: sum([Sales])

             

            SOrt.png

             

            Hope this helps,

            Justin.

            sum([Sales]