Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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)
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.
[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.
If you click Actions, bottom left of your post it should give you options for Edit
I tried that, but the link fails to load the options. It's just a continuous progress bar.
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