Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
See why IDC MarketScape names Qlik a 2025 Leader! Read more
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

converting if() to set analysis

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)

Capture.PNG

1 Solution

Accepted Solutions
sunny_talwar

Or may be this:

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

View solution in original post

5 Replies
robert_mika
Master III
Master III


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)

Not applicable
Author

No dice, both of those also give 0

Not applicable
Author

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)

Capture.PNG

sunny_talwar

Or may be this:

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

Not applicable
Author

Awesome! thanks!!!