Pie chart, count scores - lower, middle, upper

[gshockxcc]

This is a simple question, but I'm struggling with the mechanics.  I have scores for student, with fields "Attained" and "Possible".  Scores are calculated in % as Sum(Attained)/Sum(Possible).  I want to Count the number > 50%, number = 50%, and number < 50%. 

Then, I want to show this in a pie chart, so that I only have three groups.  I'm not sure if I should be counting the number of scores, or the number of students.  Either way, my expressions get complicated, and I'm not seeing a straightforward solution.

Should I create variables and use a ValueList?  Not quire sure how that would work.  I appreciate any guidance you can offer.

Thank you in advance,

[swuehl]

Try a calculated dimension like

=Aggr(

      If( Sum(Attained)/Sum(Possible) < 0.5, dual('< 50%',1),

          If(Sum(Attained)/Sum(Possible) > 0.5, dual('>50%,3),dual('=50%',2)

          )

     )

,Student)

and a single expression / measure

=Count(DISTINCT Student)

Buckets

[gshockxcc]

Stefan,

     This worked perfectly.  I created a Field called "Score" instead of students.  How can I modify this calculated dimension, to filter the scores by Subject, i.e. Mathematics?  If I put a selection filter in the App, it yields the correct results, but I want the pie chart to be static for each subject.

Here's my calculated dimension, which is working.

=Aggr(

If(  Score < 0.5, dual('< 50%',1),

          If( Score > 0.5, dual('>50%',3),dual('=50%',2)

          )

     )

, Score)

Here's my MODIFIED calculated dimension, which is working.

=Aggr(

If( {<AS_Subject = {'Mathematics'} >} Score < 0.5, dual('< 50%',1),

          If( {<AS_Subject = {'Mathematics'} >} Score > 0.5, dual('>50%',3),dual('=50%',2)

          )

     )

, {<AS_Subject = {'Mathematics'} >} Score)

Thank you in advance for your help.  Much appreciated.  Also, the Bucket link is very helpful.  Cheers.

[gshockxcc]

[EDIT] to the above.  The modified script is NOT working.  Sorry for the confusion.  I can't figure out how to modify my reply post.

[Anonymous]

If you click Actions, bottom left of your post it should give you options for Edit

[gshockxcc]

I tried that, but the link fails to load the options.  It's just a continuous progress bar.

[swuehl]

You can only use set analysis in combination with aggregation functions, like Sum(), Count() or Only()

It's hard to help you with the exact syntax without knowing a minimum about your data model, but adding Only() could solve your issue:

=Aggr(

If( Only( {<AS_Subject = {'Mathematics'} >} Score ) < 0.5, dual('< 50%',1),

          If( Only({<AS_Subject = {'Mathematics'} >} Score) > 0.5, dual('>50%',3),dual('=50%',2)

          )

     )

, Score) //no aggregation for the aggr dimension