Multiple IF condition with RAG status

[richard24best]

Hi Team,

I have requirement to create multiple conditions for different report frequencies and last accessed days

Measure - if(Isnull(max(EVENT_DATE)),'NA',
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))

Daily
>15-red
>7 - amber
<7 - green

weekly
>15 - amber
>30 - red
<15 - green

monthly
>60 - amber
>120 - red
<60 - green

Quarterly
>210 - amber
>240 - red
<210 - green

Could you please check and suggest me the best way to create the expressions with RAG status?

Thanks in advance,

Richard

[richard24best · Accepted solution]

I believe i got it thanks all

if(Frequency='Daily' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))<7,'#008580',
if(Frequency='Daily' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))>15,'#DB0011',
if(Frequency='Daily' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))='NA','#DB0011',
if(Frequency='Daily' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))>7,'#e8a215',
if(Frequency='Daily' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))<15,'#e8a215',
if(Frequency='Weekly' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))<15,'#008580',
if(Frequency='Weekly' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))>30,'#DB0011',
if(Frequency='Weekly' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))='NA','#DB0011',
if(Frequency='Weekly' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))>15,'#e8a215',
if(Frequency='Weekly' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))<30,'#e8a215',
if(Frequency='Monthly' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))<60,'#008580',
if(Frequency='Monthly' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))>120,'#DB0011',
if(Frequency='Monthly' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))='NA','#DB0011',
if(Frequency='Monthly' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))>60,'#e8a215',
if(Frequency='Monthly' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))<120,'#e8a215',
if(Frequency='Quarterly' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))<210,'#008580',
if(Frequency='Quarterly' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))>240,'#DB0011',
if(Frequency='Quarterly' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))='NA','#DB0011',
if(Frequency='Quarterly' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))>210,'#e8a215',
if(Frequency='Quarterly' and if(Isnull(max(EVENT_DATE)),999,
if(max(EVENT_DATE)<>'',networkdays(max(EVENT_DATE),today())))<240,'#e8a215'))))))))))))))))))))