topic Re: Expression help, column with total population in a table broken out by region in QlikView

Expression help, column with total population in a table broken out by region

stevelord — Mon, 09 Feb 2015 19:57:02 GMT

Hi, I have a reasonably simple table setup like so:

Dimension = Region

Expression 1 = count(distinct if (status=active and registered=yes, userid))

Expression 2 = count(distinct if(status=active, userid))

Expression 3 = expression 1 / expression 2 to get the percentage of people active in our database who are registered on our portal.

Expressions 4 and 5 requested want Expression 1 divided by the TOTAL from Expression 2, and Expression 2 divided by the TOTAL from Expression 2. (People want to know what percent of the total population Expressions 1 and 2 represent.)

I've tried a couple of expressions with set analysis syntax in them to try to make them ignore the region aggregation of the table, but it's not taking.

For a workaround, I have a thought to just setup a variable input box and let a couple of expressions reference that input box as a workaround, but would rather have the table to work on it's own. (Workaround would have someone type the total from Expression 2 into the input box to make Expressions 4 and 5 update. )

Thanks in advance for the expression that works!

Re: Expression help, column with total population in a table broken out by region

jpapador — Mon, 09 Feb 2015 19:59:13 GMT

Instead of using TOTAL have you tried using ALL:

Expression 1 = count(distinct if (status=active and registered=yes, userid))

Expression 2 = count(distinct ALL if(status=active, userid))

Re: Expression help, column with total population in a table broken out by region

maxgro — Mon, 09 Feb 2015 20:08:27 GMT

for total expression 2 you can try

count({$ <status={active}>} distinct TOTAL userid)

Re: Expression help, column with total population in a table broken out by region

NickHoff — Mon, 09 Feb 2015 20:44:44 GMT

Expression 1 = count(distinct if (status=active and registered=yes, userid))

Try: IF(Status = 'active' AND Registered = 'yes', COUNT(DISTINCT userid), userid)

Expression 2 = count(distinct if(status=active, userid))

Try: IF(Status = 'active', COUNT(DISTINCT userid),userid)

Expression 3 = expression 1 / expression 2 to get the percentage of people active in our database who are registered on our portal.

Try: [NameofExpression1]/[NameofExpression2]

edit: for expression 4 and 5 just change 1 and 2 instead of COUNT(DISTINCTuserid) to SUM(TOTAL userid)

Re: Expression help, column with total population in a table broken out by region

stevelord — Mon, 09 Feb 2015 21:06:22 GMT

Sorry, I think I misexplained.

Expressions 1 2 and 3 are okay as they are, I just put them in for context.

Expression 4 and 5 not listed are ones I'm trying to work out

Now if the total line on expression 2 says 800 people are present across all the regions, I want expression 4 to be Expression 1 / 800 all the way down and expression 5 to be Expression 2 / 800.

Dimension Exp1 Exp2 Exp3 Exp4 need help Exp5 need help

Region ActiveRegistered Active %ofRegionParticipated RegionParticipated%ofCo. RegionActive%ofCo.

400 800 50% 50% 100%

North 100 200 50% 12.50% 25%

South 50 200 25% 6.25% 25%

East 50 200 25% 6.25% 25%

West 200 200 100% 25.00% 25%

Re: Expression help, column with total population in a table broken out by region

stevelord — Mon, 09 Feb 2015 21:11:49 GMT

I just noticed your edit and it looks good. Testing it now and will update to correct if the chart snaps to. Thanks! (Amd I will hand out helpfuls to whoever else came close. Alot of responses in a short time. )

edit: count worked instead of sum, but the main part of using Total to get it around the regional aggregation was solved.

Re: Expression help, column with total population in a table broken out by region

NickHoff — Mon, 09 Feb 2015 21:17:57 GMT

I misunderstood your requirements, that won't work for 4 and 5. However, if your first 3 expressions are working correctly. You can just use the expression name in [ ] with your calculation by another expression.

Re: Expression help, column with total population in a table broken out by region

stevelord — Mon, 09 Feb 2015 21:32:35 GMT

the edit suggested for 4 and 5 worked like a charm!

Re: Expression help, column with total population in a table broken out by region

stevelord — Mon, 09 Feb 2015 21:35:14 GMT

I gave this variation a shot just to see, but it was a no go. Here's the variation that worked ultimately.

Expression 1 = count(distinct if (status=active and registered=yes, userid))

Expression 2 = count(Total if(status=active, userid))