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

[stevelord]

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!

[jpapador]

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))

[maxgro]

for total expression 2 you can try

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

[NickHoff · Accepted solution]

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)

[stevelord]

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%

[stevelord]

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.

[NickHoff]

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.

[stevelord]

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

[stevelord]

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))