0 Replies Latest reply: Feb 24, 2015 11:27 AM by Alireza Behdadian RSS

    Customer segmentation using avg & stdev

    Alireza Behdadian

      Hello,

       

      I'm still a nooby in Qlikview. I'm recently trying to do some basic customer segmentation. I'm trying to compare each customer's total sales with total average, then create a binary High/Low value (High if above total average and low  if else). I tried using:

      if(Sum(sales)>=avg(aggr(sum(sales) , customer-code)),'High','Low')

      but i didn't get the result that I wanted. Using TOTAL didn't help either. So instead I had to use:

      if(sum(sales)>=(Sum(Total sales)/Count(Total DISTINCT customer-code)),'High','Low')

      which works but it's not pretty at all! and also does not help me in my future segmentation. In the next step I'd like to segment customers in 3 segments: very high, high, low. I need something like this:

      if(sum(sales)> average of all customers + 2 stdev of all customers, 'very high',

           if(sum(sales) > average of all customers  and sum(sales)<average of all customers + 2 stdev of all customers,'high',

                     'low')

       

      Thanks in advance

       

      Ali