Set Analysis on previous month help

[#1]

Hi,

im using the following expression to set the colour of a text box depending on a score :

=if(num(sum(score)/sum([Score Target]))=1,RGB(0,255,0),

if(num(sum(score)/sum([Score Target]))=0,RGB(255,128,0),RGB(255,0,0)))

This is working fine.  What I need to try and do is create three more text boxes to show the colour rating of previous months scores eg, For last month show a coloured textbox for April, A coloured text box for March and a coloured Text Box for February. 

I basically need to know the three previous months trend (1 coloured text box for each previous month).

My dates within my data model are  :

Also, if my initial If statement can be simplified in anyway using set analysis - that would be a great help too.

Many thanks for your time.

Fiorano    

[Gysbert_Wassenaar · Accepted solution]

Simplified no, more like the opposite. I've used max(cal_month) so you get the maximum of the possible month values, but perhaps you want something like num(month(today())) instead.

=if(num(sum({<cal_month={'$(=max(cal_month))'}>}score)/sum({<cal_month={'$(=max(cal_month))'}>}[Score Target]))=1,RGB(0,255,0),

if(num({<cal_month={'$(=max(cal_month))'}>}sum(score)/sum({<cal_month={'$(=max(cal_month))'}>}[Score Target]))=0,RGB(255,128,0),RGB(255,0,0)))

=if(num(sum({<cal_month={'$(=max(cal_month)-1)'}>}score)/sum({<cal_month={'$(=max(cal_month)-1)'}>}[Score Target]))=1,RGB(0,255,0),

if(num({<cal_month={'$(=max(cal_month)-1)'}>}sum(score)/sum({<cal_month={'$(=max(cal_month)-1)'}>}[Score Target]))=0,RGB(255,128,0),RGB(255,0,0)))

=if(num(sum({<cal_month={'$(=max(cal_month)-2)'}>}score)/sum({<cal_month={'$(=max(cal_month)-2)'}>}[Score Target]))=1,RGB(0,255,0),

if(num({<cal_month={'$(=max(cal_month)-2)'}>}sum(score)/sum({<cal_month={'$(=max(cal_month)-2)'}>}[Score Target]))=0,RGB(255,128,0),RGB(255,0,0)))

[#3]

HI Gysbert,

Thanks for the solution.  I've tested the following expression to calculate the score rating for February :

=if(num(sum({<cal_month={'$(=num(month(today()))-3)'}>}score)/sum({<cal_month={'$(=num(month(today()))-3)'}>}[Score Target]))=1,RGB(0,255,0),

if(num({<cal_month={'$(=num(month(today()))-3)'}>}sum(score)/sum({<cal_month={'$(=num(month(today()))-3)'}>}[Score Target]))=0,RGB(255,128,0),RGB(255,0,0)))

No 'colour rating' is happening to the text box.  Conversely, if I use my original expression and use listboxes to select 'Feb' then the textbox turns Red.

Be very grateful for your thoughts on this.

Many thanks,

Fiorano

*****************************************

EDIT - The problem is with my data! All working correctly now.!

Many thanks

Fiorano