Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
Hi Jyothish
Apologies for the confusion.
Sample data set will be as such:
My pie chart should show something along the lines of:
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.
Give a try with my expression.
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?
Hi Chay,
Pfa sample.
Br,
KC
Thanks, working well. Only issue is that the last '=>2' was causing an error, so I'm simply using >1
Yes, it was fixed in the attached app.
Br,
KC