topic Re: Creating calculated dimension in QlikView

Creating calculated dimension

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Creating calculated dimension

jyothish8807 — Wed, 16 Nov 2016 05:52:27 GMT

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,

Re: Creating calculated dimension

jyothish8807 — Wed, 16 Nov 2016 06:08:16 GMT

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,

Re: Creating calculated dimension

Wed, 16 Nov 2016 06:12:08 GMT

Hi Jyothish

Apologies for the confusion.

Sample data set will be as such:

My pie chart should show something along the lines of:

Re: Creating calculated dimension

jonathandienst — Wed, 16 Nov 2016 06:15:44 GMT

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.

Re: Creating calculated dimension

jyothish8807 — Wed, 16 Nov 2016 06:18:37 GMT

Give a try with my expression.

Re: Creating calculated dimension

Wed, 16 Nov 2016 06:22:31 GMT

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?

Re: Creating calculated dimension

jyothish8807 — Wed, 16 Nov 2016 06:35:16 GMT

Hi Chay,

Pfa sample.

Br,

Re: Creating calculated dimension

Wed, 16 Nov 2016 06:47:09 GMT

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

Re: Creating calculated dimension

jyothish8807 — Wed, 16 Nov 2016 08:03:43 GMT

Yes, it was fixed in the attached app.

Br,

Re: Creating calculated dimension

Wed, 16 Nov 2016 09:05:26 GMT

Thank you very much