topic Re: Average wise bucket in QlikView

Average wise bucket

dhanu_today — Tue, 29 Apr 2014 09:54:27 GMT

Hi Everyone ,

I have the fields like direction,ID,SAL.I have calculated the average by sum(sal)/Count(Total ID)

After getting above result i have to show the ID's count bucket wise based on the average calculated above.

DIRECTION	Average(i.e. sum(sal)/count(Total ID)	Count(ID)	SAL<=0	SAL>0 AND SAL<=10000	SAL>10000 AND SAL<=20000	SAL>20000 AND SAL<=50000	SAL>=50000 AND SAL<=100000	SAL>=100000
S	8993.17	7	1	5		1
N	18901.05	6	1	2	1	1	1
E	1381419	7	1	3	1	2
W	10128503.04	4				1	1	2

Here am attaching the sample data as well.

Thanks in adv,

Dhanu

Re: Average wise bucket

MK_QSL — Tue, 29 Apr 2014 09:56:53 GMT

what is the difference between these two?

Count-Bucket wise format

Re: Average wise bucket

dhanu_today — Tue, 29 Apr 2014 10:04:27 GMT

Thanks Manish For qucik response.But in this criteria I have to get the above mentioned format based on the average

value for their ID's. But in the earlier one we directly created the bucket based on the sum(sal) with respect their ID's

I hope you got the logic.

Thanks,

Dhanu

Re: Average wise bucket

MK_QSL — Tue, 29 Apr 2014 10:15:39 GMT

You mean to say instead of

COUNT of ID where Sal <=0

you need

COUNT of ID where SUM(Sal)/Count(TOTAL ID) <=0

Re: Average wise bucket

dhanu_today — Tue, 29 Apr 2014 10:23:22 GMT

Correct Manish....I have to show the ID's count based on the average value.

Dhanu

Re: Average wise bucket

MK_QSL — Tue, 29 Apr 2014 10:28:12 GMT

Are you sure you have given correct output in OUTPUT FORMAT worksheet?

Check below...

SAL<=0

DIRECTION	Average(i.e. sum(sal)/count(Total ID)	Count(ID)	SAL<=0
S	8993.17	7	1
N	18901.05	6	1
E	1381419	7	1
W	10128503.04	4

Re: Average wise bucket

vikasmahajan — Tue, 29 Apr 2014 10:40:04 GMT

You can use calculated Dimensions using following expression for buckets

=if((num(vDateForAgeing)-[Due Date CLE])<=0 ,'Not Due',

if(IsNull([Due Date CLE]) OR [Due Date CLE]='','Not Due',

if((num(vDateForAgeing)-[Due Date CLE])>=1 and (num(vDateForAgeing)-[Due Date CLE])<=30,'1-30',

if((num(vDateForAgeing)-[Due Date CLE])>=31 and (num(vDateForAgeing)-[Due Date CLE])<=60,'31-60',

if((num(vDateForAgeing)-[Due Date CLE])>=61 and (num(vDateForAgeing)-[Due Date CLE])<=90,'61-90',

if( (num(vDateForAgeing)-[Due Date CLE])>=91 and (num(vDateForAgeing)-[Due Date CLE])<=180,'91-180',

if( (num(vDateForAgeing)-[Due Date CLE])>=181,'Above 181',

0)))))))

Vikas

Re: Average wise bucket

dhanu_today — Tue, 29 Apr 2014 10:48:26 GMT

Its My typo....instead of SAL i have to take Based on this SUM(Sal)/Count(TOTAL ID) <=0 etc.... i have to show the format above.

Thanks,

Dhanu

Re: Average wise bucket

MK_QSL — Tue, 29 Apr 2014 10:49:34 GMT

My question is how you get <= 0 for S, N and E?

Can you please verify the same?

Re: Average wise bucket

dhanu_today — Tue, 29 Apr 2014 11:03:26 GMT

In the s-100(ID)--0.05 and N-0.08--101 and E-0--102

So am getting the along with <=0 bucket and same way i have to apply for rest of the buckets.

Dhanu

Re: Average wise bucket

MK_QSL — Tue, 29 Apr 2014 11:08:34 GMT

But as per my knowledge

0.05

0.08

both are >= 0 not <= 0

Can you please check again and upload a correct file?

Re: Re: Average wise bucket

dhanu_today — Tue, 29 Apr 2014 11:26:46 GMT

Hi Revised data .

Thanks,

Dhanu

Re: Average wise bucket

MK_QSL — Tue, 29 Apr 2014 11:42:00 GMT

Still looking wrong.. but use enclosed file..