converting if() to set analysis

[#1]

For some reason the original expression works perfectly, however when I attempt to convert to set analysis I get zero? All the if's are really hitting performance, so am trying to convert all to set analysis...

Old Current Week:

     =COUNT(DISTINCT if(MFG_STARTED='YES' AND INF_DATE_WEEK_OF=WeekStart(vDenConnectScorecard,0,-1),COI))

New Current Week:

     =COUNT(DISTINCT {$<MFG_STARTED={'YES'},INF_DATE_WEEK_OF={'$(=WeekStart(vDenConnectScorecard,0,-1))'}>}COI)

[robert_mika]

Try

  =COUNT(DISTINCT {$<MFG_STARTED={'YES'},INF_DATE_WEEK_OF={"=$(WeekStart($(vDenConnectScorecard),0,-1))"}>}COI)

or

  =COUNT(DISTINCT {$<MFG_STARTED={'YES'},INF_DATE_WEEK_OF={"=$(WeekStart(vDenConnectScorecard,0,-1))"}>}COI)

[#3]

No dice, both of those also give 0

[#4]

interestingly this does work at the day level...

old:

=COUNT(DISTINCT if(MFG_STARTED='YES' AND INF_DATE=date(vDenConnectScorecard),COI))

new:

=count(distinct {$<MFG_STARTED={'YES'},INF_DATE={'$(=date(vDenConnectScorecard))'}>}COI)

[sunny_talwar · Accepted solution]

Or may be this:

=Count(DISTINCT {$<MFG_STARTED={'YES'}, INF_DATE_WEEK_OF = {"=INF_DATE_WEEK_OF = WeekStart(vDenConnectScorecard, 0, -1)"}>} COI)

[#6]

Awesome! thanks!!!