topic Re: Ranging Spend Dimension in QlikView

Ranging Spend Dimension

Anonymous — Wed, 11 Nov 2015 21:28:42 GMT

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 — Wed, 11 Nov 2015 22:06:14 GMT

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 — Thu, 12 Nov 2015 07:59:37 GMT

Thanks alot Swuehl.

Anonymous — Thu, 12 Nov 2015 08:24:18 GMT

nothing better than suggested by swuehl

Anonymous — Thu, 12 Nov 2015 08:27:32 GMT

Can i Use Class Function to achieve the same result ?

Anonymous — Thu, 12 Nov 2015 09:46:02 GMT

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 — Thu, 12 Nov 2015 12:09:10 GMT

Thanks alot.