topic Re: Group by Clause in QlikView

Group by Clause

abhaysingh — Mon, 11 Aug 2014 07:57:34 GMT

How to use Group by Clause

i want to show Customer Count In Bucket Analysis.

My sales Buckets are

<10K

>10K to <=30K and so on

i want to show count of customer who had done sales accordingle,

e.g. if 4 customer did Total sales less than 10K in a month than they should come in Bucket of <10K.

Kindly help.

Re: Group by Clause

Mon, 11 Aug 2014 08:06:04 GMT

You can create bucket by hard coding at script editor

on script editor type

load *,

if(sum(sales)<10,000,'<10k',if(sum(sales)>10000 and sum(sales)<=30000,'>10k to <=30k',>30k)) as bucket,

resident yourtablename;

and create a bar chart and in the dimension add bucket to the dimension field and in the expression tab type following expression

count(Customer)

regards

bhawna

Re: Group by Clause

simenkg — Mon, 11 Aug 2014 08:07:41 GMT

you need a group by clause. If this is the way you want to go, then

if(sum(sales)<10,000,'<10k',if(sum(sales)>10000 and sum(sales)<=30000,'>10k to <=30k',>30k)) as bucket,

resident yourtablename

Group by Customer;

Re: Group by Clause

Anonymous — Mon, 11 Aug 2014 08:13:43 GMT

hi Abhay,

U can make use of Class function : Class() to get the result u nid

Regardz,

Vaibz...

Re: Group by Clause

tresB — Mon, 11 Aug 2014 08:14:17 GMT

Use class() (if the classes are qui-distant, else IF)to create the classes in calculated dimension or a new field in the script, then in expression simple count() should work.

Re: Group by Clause

abhaysingh — Mon, 11 Aug 2014 09:04:05 GMT

@ script level sum function is giving me error

Re: Group by Clause

simenkg — Mon, 11 Aug 2014 09:07:24 GMT

Because you need group by.

Sum is an aggregate function so it needs a group by statement.

Add Group by Customer

after your FROM clause.