5 Replies Latest reply on May 2, 2018 4:18 PM by Okechukwu Ossai

# LOD Conditional Formatting Max(date)

Take a look at school B, where you see the words blue and yellow, it should show only blue because it's the latest date 05-02-2018.

I know I need to do a LOD with MAX(Date), but the variations I am trying out is not working. Here are my current calculations...

Overall Tiers

[CF Interest to Current Shortfall]+(2*[CF Projected Shortfall])/3

CF Overall Tiers

IF [Overall Tiers]>=3.5 then 'blue'

elseif [Overall Tiers]>=2.5 and [Overall Tiers]<3.5 then 'green'

elseif [Overall Tiers]>=1.5 and [Overall Tiers]<2.5 then 'yellow'

elseif [Overall Tiers]<1.5 then 'red'

else ''

END

The other calculations...

CF Projected Shortfall

INT(If (SUM([Enrollment Target])-SUM([Projected Enrollment]))/SUM([Enrollment Target])>=.15 then '1'

elseif (SUM([Enrollment Target])-SUM([Projected Enrollment]))/SUM([Enrollment Target])>=.05 AND (SUM([Enrollment Target])-SUM([Projected Enrollment]))/SUM([Enrollment Target])<.15 THEN '2'

elseif (SUM([Enrollment Target])-SUM([Projected Enrollment]))/SUM([Enrollment Target])>=.01 AND (SUM([Enrollment Target])-SUM([Projected Enrollment]))/SUM([Enrollment Target])<.05 then '3'

elseif (SUM([Enrollment Target])-SUM([Projected Enrollment]))/SUM([Enrollment Target])<.01 then '4'

else ''

end)

CF Interest to Current Shortfall

INT(IF SUM(ABS([Current Shortfall]))>SUM([Interest]) then '1'

elseif SUM([Interest])>= SUM(ABS([Current Shortfall])) AND SUM([Interest])< SUM(ABS([Current Shortfall]))*2 then '2'

elseif SUM([Interest])>= SUM(ABS([Current Shortfall]))*2 AND SUM([Interest])< SUM(ABS([Current Shortfall]))*2.5 then '3'

elseif SUM([Interest])>= SUM(ABS([Current Shortfall]))*2.5 then '4'

else '0'

end)

• ###### 1. Re: LOD Conditional Formatting Max(date)

Shinichiro Murakami it feels you helped me with something like this before, but it tried applying the LOD Max(date) solution but it keeps returning an error. If you have a chance, I appreciate your feedback on this!

• ###### 2. Re: LOD Conditional Formatting Max(date)

Hi Diogo,

I'm not familiar with your workbook. However, if you want to perform the calculations for date = max(date) then check if the formula below will work for you.

[CF Overall Tiers]

{FIXED [School Code], [Date], [Grade]: MIN(IF [Date] = {FIXED [School Code]: MAX([Date])} THEN

IF {FIXED [School Code], [Date], [Grade]: [Overall Tiers]} >=3.5 THEN 'blue'

ELSEIF {FIXED [School Code], [Date], [Grade]: [Overall Tiers]} >=2.5 and {FIXED [School Code], [Date], [Grade]: [Overall Tiers]} <3.5 then 'green'

ELSEIF {FIXED [School Code], [Date], [Grade]: [Overall Tiers]} >=1.5 and {FIXED [School Code], [Date], [Grade]: [Overall Tiers]} <2.5 then 'yellow'

ELSEIF {FIXED [School Code], [Date], [Grade]: [Overall Tiers]} <1.5 then 'red'

ELSE '' END

END)}

Hope this helps.

Ossai

• ###### 3. Re: LOD Conditional Formatting Max(date)

Thank you! That’s a lot of LODs, I am sure we can simplify it. The correct solution for CF Overall Tiers should return only ONE value (color name) of the latest date.

School C will be blank

School D will be blue

School E will be yellow

Thanks again!

• ###### 4. Re: LOD Conditional Formatting Max(date)

Now I get you. The formula below should work.

[CF Overall Tiers]

{FIXED [School Code]: MIN(IF [Date] = {FIXED: MAX([Date])} THEN

IF {FIXED [School Code], [Date]: [Overall Tiers]} >=3.5 THEN 'blue'

ELSEIF {FIXED [School Code], [Date]: [Overall Tiers]} >=2.5 and {FIXED [School Code], [Date]: [Overall Tiers]} <3.5 then 'green'

ELSEIF {FIXED [School Code], [Date]: [Overall Tiers]} >=1.5 and {FIXED [School Code], [Date]: [Overall Tiers]} <2.5 then 'yellow'

ELSEIF {FIXED [School Code], [Date]: [Overall Tiers]} <1.5 then 'red'

ELSE '' END

END)}

School C is Null. I have filtered out all Nulls. If you want to see School C then remove the filter.

Hope this helps.

Ossai

• ###### 5. Re: LOD Conditional Formatting Max(date)

You could do the calculation in two steps if you want to make the formula more readable.

Step 1: Create calculated field [Overall Tiers LOD]

{FIXED [School Code], [Date]: [Overall Tiers]}

Step 2: Create calculated field [CF Overall Tiers]

{FIXED [School Code]: MIN(IF [Date] = {FIXED: MAX([Date])} THEN

IF [Overall Tiers LOD] >=3.5 THEN 'blue'

ELSEIF [Overall Tiers LOD] >=2.5 and [Overall Tiers LOD] <3.5 then 'green'

ELSEIF [Overall Tiers LOD] >=1.5 and [Overall Tiers LOD] <2.5 then 'yellow'

ELSEIF [Overall Tiers LOD] <1.5 then 'red'

ELSE '' END

END)}

Hope this helps.

Ossai

1 of 1 people found this helpful