Average wise bucket

[dhanu_today]

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

[MK_QSL]

what is the difference between these two?

Count-Bucket wise format

[dhanu_today]

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

[MK_QSL]

You mean to say instead of

COUNT of ID where Sal <=0

you need

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

[dhanu_today]

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

Dhanu

[MK_QSL]

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

Check below...

SAL<=0

1

1

1

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

[vikasmahajan]

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

[dhanu_today]

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

[MK_QSL]

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

Can you please verify the same?

[dhanu_today]

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