Ranging Spend Dimension

[Anonymous]

Data set currently has a significant amount of payments by single suppliers creating a long tail when looking at supplier by spend.

Spend dimension will categorise vendors spend into the following spend categories:

•    >$10m

•    $1m – 10m (upper threshold deliminter of $10m)

•    $500k – 1m (upper threshold deliminter of $999,999.99)

•    $100k - 500k (upper threshold deliminiter of $499,999.99)

•    $50k – 100k (upper threshold delimiter of $99,999.99)

•    $10k – 50k (upper threshold deliminter of $49,999.99)

•    $5k – 10k (upper threshold delimiter of $9,999.99)

•    $1k – 5k (upper threshold delimiter of $4,999.99)

•    <$1k (less than or equal to $999.99)

I Use the field ‘amount’ to form the basis of the spend threshold

How to make it happen on Edit Script.

[swuehl · Accepted solution]

A simple approach would be

LOAD amount,

          if(amount <= 999.99, dual('<$1k', 1),

            if(amount <= 4999.99, dual('$1k -- 5k',2),

              if(amount <= 9999.99, dual('%5k -- 10k',3),

               if(amount <= 49999.99, dual('$10k -- 50k',4),

                if(amount <= 99999.99, dual('$50k -- 100k',5),

                  if(amount <= 499999.99, dual('$100k -- 500k',6),

                    if(amount <= 999999.99, dual('$500k -- 1m',7),

                         if(amount <= 10000000, dual('$1m -- 10m',8) ,dual('>$10m',9) ))))))))

                                        AS SpendClass,

          ...

FROM YourSource;

[Anonymous]

Thanks alot Swuehl.

[Anonymous]

nothing better than suggested by swuehl

[Anonymous]

Can i Use Class Function to achieve the same result ?

[Anonymous]

You can, but the differenece value that you are giving with IF, is not possible in Class()

=Class(Amount,50), it wud give you 0-50,50-100,100-150,150-200 and so on...

[Anonymous]

Thanks alot.