topic Re: Location wise Avg in QlikView

Location wise Avg

Fri, 31 Jul 2015 06:51:34 GMT

Hi All,

Avg Per day per Person should be bucketed

Data :-

Date	Location	Name	Handled
7/1/2015	Blr	Raj 1	1500
7/1/2015	Chn	Raj 2	1000
7/1/2015	TJ	Raj 3	600
7/1/2015	Chn	Raj 4	1000
7/3/2015	TJ	Raj 3	1000
7/3/2015	Blr	Raj 1	900
7/6/2015	Chn	Raj 2	500
7/6/2015	Blr	Raj 5	1000

Avg Per person Per day to be bucketed as given below.

Location	Row Labels	Average of Handled	Bucket
Blr	Raj 1	1200	1000 - 1500
Chn	Raj 2	750	500 - 1000
TJ	Raj 3	800	500 - 1000
Chn	Raj 4	1000	1000 - 1500
Blr	Raj 5	1000	1000 - 1500

Final Output:

Location	0 - 500	500 - 1000	1000 - 1500	> 1500
Blr			2
Chn		1	1
TJ		1

Re: Location wise Avg

puttemans — Fri, 31 Jul 2015 06:56:08 GMT

Hello Rajesh,

And if you take location as a dimension, and '= count(Handled)' as expression in a chart, you don't get the result?

Re: Location wise Avg

jonathandienst — Fri, 31 Jul 2015 06:58:48 GMT

Create a bucket field using an interval match or using code like this:

LOAD Date,

Location,

Name,

Handled,

If(Handled < 500, Dual('0-500', 0),

If(Handled < 1000, Dual('500-1000', 500),

If(Handled < 1500, Dual('1000-1500', 1000),

Dual('>1500', 1500)))) As Bucket

FROM ....

Now use Bucket as the cross dimension and sort it numerically (the duals will keep the order correct).

Re: Location wise Avg

qlikmsg4u — Fri, 31 Jul 2015 07:03:53 GMT

find the attached file

Re: Location wise Avg

Fri, 31 Jul 2015 07:04:36 GMT

NO this will not work as I wanted Avg Handled and not Handled.

Raj 1 Avg Handled is 1200.How do i bucket teh Names basis teh avg handled as the bucket given by you

Re: Location wise Avg

Fri, 31 Jul 2015 07:06:05 GMT

NO this will not work as I wanted Avg Handled and not Handled.

Raj 1 Avg Handled is 1200.How do i bucket teh Names basis teh avg handled as the bucket given by you

Location	0 - 500	500 - 1000	1000 - 1500	> 1500
Blr			2
Chn		1	1
TJ		1

Location wise Avg

Re: Location wise Avg

jonathandienst — Fri, 31 Jul 2015 07:11:23 GMT

Average per day, per person or both?

Re: Location wise Avg

Fri, 31 Jul 2015 07:20:13 GMT

Avg Per day per Person should be bucketed

Data :-

Date	Location	Name	Handled
7/1/2015	Blr	Raj 1	1500
7/1/2015	Chn	Raj 2	1000
7/1/2015	TJ	Raj 3	600
7/1/2015	Chn	Raj 4	1000
7/3/2015	TJ	Raj 3	1000
7/3/2015	Blr	Raj 1	900
7/6/2015	Chn	Raj 2	500
7/6/2015	Blr	Raj 5	1000

Avg Per person Per day to be bucketed as given below.

Location	Row Labels	Average of Handled	Bucket
Blr	Raj 1	1200	1000 - 1500
Chn	Raj 2	750	500 - 1000
TJ	Raj 3	800	500 - 1000
Chn	Raj 4	1000	1000 - 1500
Blr	Raj 5	1000	1000 - 1500

Final Output:

Location	0 - 500	500 - 1000	1000 - 1500	> 1500
Blr			2
Chn		1	1
TJ		1

Re: Location wise Avg

puttemans — Fri, 31 Jul 2015 07:26:11 GMT

sorry,

per day or per location? You show per location.

I've managed to calculate the averages, but have difficulties putting it in the right output you want.

Re: Location wise Avg

Fri, 31 Jul 2015 07:30:44 GMT

Hi,

Even teh averages is not correct

Re: Location wise Avg

puttemans — Fri, 31 Jul 2015 07:32:52 GMT

Latest version, with approximation of your needs I guess.

Kind regards,

Johan

Re: Location wise Avg

puttemans — Fri, 31 Jul 2015 07:37:24 GMT

In the previous version, I used day as well, now I only used location.

For TJ, data differ since QV makes a difference between Raj3 and Raj 3. If you change this small error in the source data, it'll give you the desired output.

Regards,

Johan

Re: Location wise Avg

Kushal_Chawda — Fri, 31 Jul 2015 07:48:55 GMT

Please see the attached

Re: Location wise Avg

Fri, 31 Jul 2015 09:18:30 GMT

HI,

One small query in your query you have used class function hence it is getting bucketed at an equal interval of 500 , but if i had to vary how to i do it.

Re: Location wise Avg

Kushal_Chawda — Fri, 31 Jul 2015 09:22:23 GMT

Your bucket should follow the Particular interval. In your case it was 500. Now if you want 0-400, 400-800 then change 500 to 400