1 Reply Latest reply: Jul 28, 2017 12:44 PM by Joshua Russin RSS

    MTD missed targets

    Joshua Russin

      I am using this formula to retrieve the total amount of days that we missed our target.

      If I leave the Program_Dash={'Mechanical Inspection'} in the formula, I get a 0 as an answer.

      If I take out the Program_Dash, it gives me the correct total for all of the areas.

       

      Count({$<Processed_Dash = {"=Processed_Dash < Daily_Target_Dash"}, Program_Dash={'Mechanical Inspection'},

      Month = {"$(=Month(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))))"},

      Year = {"$(=Year(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))))"}>}

          Processed_Dash)

       

      I have a sheet where I use a filter for selecting the "Program_Dash" and here is the formula I am using.

      I am just trying to create a sheet that doesn't use a filter. The example I am using for Program_Dash is "Mechanical Inspection"

       

       

      =Sum(Aggr(

      Count({<Processed_Dash = {"=Processed_Dash < Daily_Target_Dash"}, Month = {"$(=Month(FirstWorkDate(ConvertToLocalTime(Date(now()),  'GMT-05:00'),1,$(vHolidays))))"}, Year = {"$(=Year(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT 05:00'),1,$(vHolidays))))"}>} DISTINCT Date_Dash), Date_Dash, Program_Dash, Status_Dash))

       

       

      Help please?

        • Re: MTD missed targets
          Joshua Russin

          Created a Flag in the script like this

          If(Processed_Dash < Daily_Target_Dash, 1,0) as Daily_Target_Flag

           

          Using this expression to count

          Count({<Daily_Target_Flag = {'1'}, Program_Dash={'Mechanical Inspection'},

          Month = {"$(=Month(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))))"},

          Year = {"$(=Year(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))))"}>}

          DISTINCT Date_Dash)