topic Re: converting if() to set analysis in QlikView

converting if() to set analysis

Tue, 18 Oct 2016 20:34:31 GMT

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)

Re: converting if() to set analysis

robert_mika — Tue, 18 Oct 2016 22:01:46 GMT

Try

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

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

Re: converting if() to set analysis

Tue, 18 Oct 2016 22:38:46 GMT

No dice, both of those also give 0

Re: converting if() to set analysis

Tue, 18 Oct 2016 22:48:04 GMT

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)

Re: converting if() to set analysis

sunny_talwar — Tue, 18 Oct 2016 22:49:06 GMT

Or may be this:

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

Re: converting if() to set analysis

Tue, 18 Oct 2016 23:14:19 GMT

Awesome! thanks!!!