2 Replies Latest reply: Jul 14, 2016 5:21 AM by Weng Cheun Fong RSS

    Create a Classification Band

    Weng Cheun Fong

      Greetings,

       

      I have 2 fields from my file: Actual Sales Amount, Actual Cost Amount

       

      I would like to classify my customers based on the GM%, which is GM% = (Actual Sales Amount - Actual Cost Amount)/Actual Sales Amount

       

      If the GM% is more than 50%, then the customer is an A

      If the GM% is between 40% to 50%, then customer is a B

      If the GM% is between 20% to 40%. then customer is a C

      If the GM% is between 0% to 20%, then customer is a D

      If the GM% is below 1%, then customer is a E

       

      Appreciate your advice on how best to reflect the above in Qlik Sense.

       

      Many thanks!

        • Re: Create a Classification Band
          Gysbert Wassenaar

          Calculate a Classification field in the script and then use that field as dimension in a chart with for example count(distinct Customer) as measure.

           

          And think about what makes a customer an E. GM% below 1 or below 0? There's an overlap in your definition with D.

           

          MyTable:

          LOAD

               *,

               If(GM%<0.01, 'E',

                   If(GM%<=0.2 , 'D',

                       If(GM%<=0.4 , 'C',

                           If(GM%<=0.5 , 'B', 'A')))) as Classification;

          LOAD

               Customer,

               [Actual Sales Amount],

               [Actual Cost Amount]

               1-[Actual Cost Amount]/[Actual Sales Amount] as GM%

          FROM

               ...source...

               ;

          • Re: Create a Classification Band
            Weng Cheun Fong

            Thanks GD! I will try it out and revert if I have further queries. Cheers!