Age banding

Previous Member

Hi Board community.

I need to show claim amount by groups of ages of the claim e.g. 0-16, 16-31, 31-46.....

How do I do this kind of age banding in Board?

 

Regards

Thora

Alexander Kappes

Dear Thora Hirst,

 

your request depends to your database structure and depending sources of data.

 

Are the claims managed in BOARD or loaded from third party solution?

 

Can you get the Age information from the source system?

 

Is so, you can do the following, insert age dimension in BOARD where the age of the claim is saved. Insert an aggregation to the age as relationship.

 

This aggregation has the wished structure (0-16, 16-31, 31-46) finally you have only to set the relationship between the age and your aggregation and can use the aggregation in your dashboards.

 

Hope it helps

 

regards

 

Alex

Previous Member

Thank you for your answer Alexander Kappes

 

I am loading the claims from sql and I should be able to get the age information so I will follow you suggestion.

 

Best Regards,

Thora

Fabio Donatellis

Hi Thora Hirst

 

In this article  Sample: Time Functions,  you can find several applications of the time functions and a simple example.

I think could be a good inspiration.

 

Fabio

Unknown

Hi Thora Hirst,

  Since you mentioned the datasource is SQL, you could also calculate the bands in your source query. To make things simpler in BOARD, you may want to create a database view like the following.

 

CREATE VIEW dbo.vw_MyView AS

SELECT A.Age,
   CASE
     WHEN A.Age BETWEEN 0 AND 16 THEN 'Group1'
     WHEN A.Age BETWEEN 17 AND 31 THEN 'Group2'
     WHEN A.Age BETWEEN 32 AND 46 THEN 'Group3'
     ELSE 'Group4'
   END as AgeBand
FROM dbo.tblFact A

 

  By using CASE and BETWEEN statements, the banding can be done in the query as a derived column. You would probably want to add other key and data columns to the view too. 

Previous Member

Hi Bob

 

Thank you very much for your reply.

It is very helpful.

 

Regards,

Thora

Previous Member

Hi,

 

You could also use a hierarchy in the relations. So in your dimension you would have an entity for every age and in a relation you would group them.

This way you can have reports on the hierarchy with the option to drill down to details.

Previous Member

Hi Robert

This is also a good idea. Thank you.

 

Regards,

Thora

Previous Member

Hi Bob Gill! I found your info useful for myself. But the real problem is to implement the idea you have stated. is there any easiest way or tool like thing to handle/solve it?

Unknown

Well, SQL Management Studio would be the tool to use for a SQL approach.  

 

If you want to keep everything in BOARD, you could use a series of data flow steps in a procedure. In each dataflow step, we would use an if statement to say if(and(20<a, a<= 25),a,0). This expression gives the bucket of 20-25.