MTD missed targets

[joshrussin]

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?

[joshrussin · Accepted solution]

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)