Colour/Scatter Chart

[#1]

Hello everyone!

I am trying to produce a scatter chart that would reflect performance of pupils. On the X-axes is the quality score they have (%) which shows number of problems they solved (solved= marked 'G' for green) and Y-axes is the number of problems solved. I want the scatter plots to reflect through colour coding their performance.

Conditions:

if a pupil has a score of >= 85% and solved >=10 problems= Green point

if score<85% and <8= Red

everything else= Yellow

Currently, I am using the following code in my colour section of the chart properties, however, it only works when a specific date and pupil are selected, and aggregates everything to be one colour when nothing or multiple (pupils/dates) are selected.

if(GetSelectedCount(Date)<>0 or GetSelectedCount([Pupil Name])<>0,



if((sum([Problem Count])>='10' and (count({<[Overall Score]={'G'}>} [Problem Reference])*100/count([Case Reference]))>='90'), RGB(0, 255, 64),

if( sum([Problem Count])<'8' or (count({<[Overall Score]={'G'}>} [Problem Reference])*100/count([Problem Reference]))<'85', RGB(255,0,0),

RGB(255, 255, 0))),



  if((sum([Problem Count Cumulative])>=(10*(count(Date))) and (count({<[Overall Score]={'G'}>} [Problem Reference])*100/count([Problem Reference]))>='90'), RGB(0, 255, 64),

  if( sum([Problem Count])<(8*(count(Date))) or (count({<[Overall Score]={'G'}>} [Problem Reference])*100/count([Problem Reference]))<'85', RGB(255,0,0),

  RGB(255, 255, 0))))

Could you please help me with this?

Thank you,

Anna

[jerem1234]

Is there a reason why first set of ifs, you use sum([Problem Count]), but for the second set, you use sum([Problem Count Cumulative]) for the first if statement?

Also could be that your using a wrong field?

For the first set you have:

(count({<[Overall Score]={'G'}>} [Problem Reference])*100/count([Case Reference]))

and you then have

(count({<[Overall Score]={'G'}>} [Problem Reference])*100/count([Problem Reference]))

but in the second you have:

(count({<[Overall Score]={'G'}>} [Problem Reference])*100/count([Problem Reference]))

and you then have

(count({<[Overall Score]={'G'}>} [Problem Reference])*100/count([Problem Reference]))

So you use Case Reference instead of Problem Reference to divide for the first one.

Can't really deduce what exactly is the problem. If you could post a sample application that demonstrates the problem, might be able to help further.

Hope this helps!

[vidyut]

Will be good if you could share a sample QVW with Data and chart please.

[#4]

Sorry, both typos- the actual code includes all problem references and not case references, and it's all Problem Count and not Problem Count Cumulative.

The problem is that when nothing is selected, I would want the scatter plot to have scatter points in different colours to highlight the overall performance of the class. However, when nothing is selected, all of the pupil scatter points are showing in red. It works when the pupil is selected, so I am assuming it somehow averages the results across pupils instead of showing them separately.

Thanks,

Anna

[#5]

Unfortunately, I can't share this data

[Clever_Anjos]

Use the Scramble feature, your data would be safe

[jerem1234 · Accepted solution]

I noticed one thing when I was creating an example with a small amount of similar data. You need to have count(distinct Date), not just count(Date).

Also you can get rid of the GetSelectedcount() of the different fields. If you select a one date, then it should become 8*count(distinct Date)=8*1.

I used the formula in the background expressions:

if(count({<[Overall Score]={'G'}>} [Problem Reference])*100/count([Problem Reference])>=90 and sum([Problem Count]) >=10*count(distinct Date), RGB(0, 255, 64),

if(count({<[Overall Score]={'G'}>} [Problem Reference])*100/count([Problem Reference]) < 85 and sum([Problem Count]) <8*count(distinct Date), RGB(255,0,0),RGB(255, 255, 0)))

Please find attached as an example.

Hope this helps!

[#8]

Thank you!!! It works:))