Creating calculated dimension

[#1]

Hi

I have 3 tables with individual information, and I am trying to create a pie chart to show family status. The key fields are as folows:

ID = ID number of individuals

Role = Parent/Child

RegNo = Household Number

I am attempting to create pie charts with the following:

- Number of children in a household (Chart should show me Count(Distinct(RegNo)) with Age <=8 and Role = Child

I am currently using the code in Expressions

COUNT( {< Age = {">=0 <=15"}, Role = {Child}  >}DISTINCT(RegNo))

In Dimension, I am trying to get the pie chart to show "0 Children", "1 Child", "2 Children", ">2 Children", however I am unsure of how to proceed.

- Number of Members in a hosuehold

Expression: COUNT (DISTINCT ( RegNo))

Dimension: Should show "1 Member", "2 Members", "3 Members"....">5 Members"

Any advice will be greatly appreciated.

[jyothish8807]

Hi Chay,

Can you share a sample data ? Then it will be easy for us to help.

Q1. What do you mean by dimension "0 Children", "1 Child", "2 Children", ">2 Children" when you are already showing only the children who are less than 15 yrs old ? I am a bit confused.

Br,

KC

[jyothish8807 · Accepted solution]

You can try something like this in Dimension:

If( Aggr(count({<Role ={'Child'}>}ID),RegNo)=0,'0 Children', if(Aggr(count({<Role = {'Child'}  >}ID),RegNo)=1,'1 Children',if(Aggr(count({<Role = {'Child'}  >}ID),RegNo)=2,'2 Children',if(Aggr(count({<Role = {'Child'}  >}ID),RegNo)=>2,'>2 Children'))))

Br,

KC

[#4]

Hi Jyothish

Apologies for the confusion.

Sample data set will be as such:

My pie chart should show something along the lines of:

[jonathandienst]

You can do it with an expression like:

If(COUNT({<Age = {">=0 <=15"}, Role = {Child}>} DISTINCT RegNo) = 0,

  Dual('0 Children', 0),

If(COUNT({<Age = {">=0 <=15"}, Role = {Child}>} DISTINCT RegNo) = 1,

  Dual('1 Child', 1),

If(COUNT({<Age = {">=0 <=15"}, Role = {Child}>} DISTINCT RegNo) = 2,

  Dual('2 Children', 2),

  Dual('>2 Children', 3)

)))

But I would recommend doing this calculation in the back-end as this sort of calculation can perform poorly in the front end if your data set is large.

[jyothish8807]

Give a try with my expression.

[#7]

Hi Jonathan

Thanks for the response. If I were to implement this in the back end, which table do you reckon I should place that code?

[jyothish8807]

Hi Chay,

Pfa sample.

Br,

KC

[#9]

Thanks, working well. Only issue is that the last '=>2' was causing an error, so I'm simply using >1

[jyothish8807]

Yes, it was fixed in the attached app.

Br,

KC