How to create Age brackets , Data coming from Bex Query.

[former_member210043]

Hi Experts ,

   I  am using the pie chart component to count  of people with  different ages. In this I need to display the Age as Age  brackets as legends and the count of people for each age  bracket in pie chart component.

The Data is coming from Bex query which having two objects : count , Age.

Age Brackets Are : Below 25, 25 to 35, 35 to 45 ,  45 to 55 , 55 and above.

How to create Age brackets  for the   Age object which coming from the bex query to display the  count of people in pie cahrt.

Please Help me.....

Thanks... in advance ..

With regards ,

Nikil

[Former Member]

Hi Nikhil,

There are a couple of ways to achieve this.

1. Create the bucket at the Bex query itself

2. - Create a WebI report on the Bex Query.

    - Create a Variable to generate the buckets based on Age object

    - Create table with Bucket variable and Count and publish the block as web service.

    - Consume the web service in Dashboards using a QaaWS connection.

3. - Get data from the Bex Query in the dashboard.

    - Generate the Bucket code using the formula below.

Bucket Code	Age	Count
=IF(B2<25,1,IF(AND(B2>=25,B2<35),2,IF(AND(B2>=35,B2<45),3,IF(AND(B2>=45,B2<55),4,5))))	20	30
	34	22
	40	20

4. In another sheet create this table using the formula for Count:

Bucket Code	Age Bucket	Count
1	Below 25	=SUMIF(Raw!$A$2:$A$50,$A2,Raw!$C$2:$C$50)
2	25 to 35	363
3	35 to 45	187
4	45 to 55	313
5	55 and above	153

Create your Pie Chart based on the above table.

You might want to check whether your version of Dashboards supports the SUMIF function. I tried on Dashboards 4.1 SP06 and found it to be working.

Regards,

Ashish K.

[former_member210043]

HI Ashish ,

Thanks a lot for the Response.. I will try the sumif function..

Regards,

Nikil

[former_member182541]

Unable to handle this is dashboard level unless you hard-code. Rather than this go to bex query and change the text of this age levels like: (below 25),(25 to 35) ....... hope this is a structure, so surely you can change the text in bex level.

[former_member210043]

Hi Suman ,

     Thanks for the response ,

if age is cmng from the bex query  as  10,15,16 17,19, 20 , 21,22 ,23,24,.........

can we consider  separate queries on the Original  Bex query  as count is  result object and in where  clause or filter as  less than 25, between 25 to 35 for  each bracket.

just like hardcoding the age brackets using filter .

If yes .. please help me..

Thanks ..

Nikil

[saurabh_sonawane]

which connection ur using

[former_member210043]

Hi Saurabh ,

BICS...connection..

Thanks

Nikil

[former_member182541]

Can't you create the structure in BEx, Can you attach the bex query output here.

[former_member210043]

Hi Suman ,

Thanks ....for the response..

I tried to bind the data to spreadsheet from Bex queries and working out to create Brackets

Thanks

Nikil

[former_member182541]

In this case you need to do grouping here for below 25,25-35.... and so on. If you can do it in dashboard level well and good, how are you grouping them. Grouping in excel level may be required more logic, that's why i am suggesting you to create the structure in bex and then calling them into the dashboard.

[former_member210043]

HI Suman,

Thanks ..

i am having the logic already same thing i am using..

Thanks

Nikil.