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

    LOD Conditional Formatting Max(date)

    Diogo Braga

      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

       

      2018-05-02_1738.png

       

      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)
          Diogo Braga

          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)
            Okechukwu Ossai

            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)
              Diogo Braga

              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)
                Okechukwu Ossai

                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)
                  Okechukwu Ossai

                  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