Hello, How can I group amount spent by customers into different sized bands?

[bazzaonline]

Hello, How can I group amount spent by customers into different sized bands?

[bazzaonline]

Sorry - to be clearer, I have a list of customers and how much they paid in a transaction and would like to group them.

0

0-1000

1000-3000

3000 plus

Thank you - its day one using qlikview

[#3]

hi

try class() function.

or try this

if(customers='A','0-1000') as field;                like this

[#4]

Hi,

Use can Class function to achieve this

for example....

=Class([Paid amount],1000)

Thanks and Regards,

Vivek

[aveeeeeee7en]

Hi

Dimension: Customers

Expression:

If(Sum(Amount)<=0,'0',

If(Sum(Amount)>0 AND Sum(Amount)<=1000,'0-1000',

If(Sum(Amount)>1000 AND Sum(Amount)<=3000,'1000-3000',

If(Sum(Amount)>3000,'3000 plus','Not Defined'))))

or, you can create buckets in the Script

Load

Customers,

If(Sum(Amount)<=0,'0',

If(Sum(Amount)>0 AND Sum(Amount)<=1000,'0-1000',

If(Sum(Amount)>1000 AND Sum(Amount)<=3000,'1000-3000',

If(Sum(Amount)>3000,'3000 plus','Not Defined')))) AS Buckets

From FileName

Group By Customers;

Regards

Aviral Nag

[bazzaonline]

Aveeeeee7en, thank I will try this.  If the file name is a like to an SQL server what would I need to write in the script?  Thank you again

[bazzaonline]

Sorry - What I am trying to do is a stacked bar chart which shows across the bottom - month of purchase.  The stacked is a count of the customers banded by the amount of the transaction.

0

0-1000

1000-3000

3000 plus.

Can I do this?

[Anonymous]

Hi Paul,

For the grouping according to different brands see below script for the example

test:

LOAD * INLINE [

    Customers, Brand,Price

    Cars1 , a  , 100

    Cars2 , b   ,300

    Cars1  ,a  ,200

    Cars2  ,b  ,400

    Cars5 , c  ,500

];

NoConcatenate

Abc:

load Customers, Brand, sum(Price) as SpentAmount

Resident test

group by Customers, Brand;

DROP Table test;

[bazzaonline]

I need to draw a chart which shows by month, the number of customer that spent between,

0

0-1000

1000-3000

3000 plus.

However I dont have the banded groups, just the amount the spent.  For example I want someone that spent 1005 to be banded in the 1000-3000 band and then display a stacked bar from counting the number of customers in each.  PLEASE HELP

[aveeeeeee7en · Accepted solution]

Hi

First create Bucket in the Script. Like this:

If(Amount<=0,'<=0',

If(Amount>0 AND Amount<=1000,'0-1000',

If(Amount>1000 AND Amount<=3000,'1000-3000',

If(Amount>3000,'3000 plus')))) AS Buckets

After Reload, Create Stacked Bar Chart:

a) Dimension: Month

b) Expression: (Create 4 Expressions)

1) Count(DISTINCT{<Buckets={'<=0'}>}Customers)                // For <=0 Bucket

2) Count(DISTINCT{<Buckets={'0-1000'}>}Customers)           // For 0-1000 Bucket

3) Count(DISTINCT{<Buckets={'1000-3000'}>}Customers)     // For 1000-3000 Bucket

4) Count(DISTINCT{<Buckets={'3000 plus'}>}Customers)       // For 3000 plus Bucket

For Stacked Bar Chart Setting see the Snapshot:

Regards

Av7en