Set Anlysis Causing Error

[#1]

Hi guys,

I am having trouble with a particular expresion and was wondering if anyone had any ideas... I would like to count the number of systems in which, in their last test, they have passed, but the pass occurred over 18 months ago. I tried using the expression:

if
 (

 (
 (only({$<DRTested = 'Pass'>}max(ContingencyDate)) < today() - 180 )

I tried using 'only' as it was causing an error putting set analysis into an if statement. Typically I would just like to do a count with set analysis like below:

count({$<ContingencyDate = { '<$(vComplianceRedDate)'}, DRTested = {"Pass"} >}SystemLocation)

where

vComplianceRedDate =date(addmonths(Today(),-18),'MMM-YY')

The problem definitely lies in the Date part as it filters to a pass before this. Has anyone got any ideas?

Regards,

[brenner_martina]

Hi, bcolgan85

Try that expression, in Set Analysis you must use this brackets { ...} again and "..." :

(only({$<DRTested = {"Pass"}>} max(ContingencyDate)) < today() - 180 )

Greeting from Martina from Germany

[brenner_martina]

Pardon ... and you can use the Num-Function, to change Date into number!

[#4]

Thanks,

My requirements have changed slightly - I would like to bring back the value for DRTested which has the maximum Contingency Date

ContingencyDate	DRTested
04/05/2010	Pass
06/03/2009	Fail
01/02/2009	Scheduled
05/08/2008	Pass

So I would like to do a RAG (Red, Amber, Green) Statusso that if the most recent Value of the result is a pass and the pass happened within the last 18 months give it a Green RAG Status, if it is a Fail - red, etc. I tried creating a calculated dimension by merging both the fields together but I'm sure there is a better option? Has anyone any ideas?

Thanks,