topic Re: Count if Set Analytics Dual Criteria for Records closed in current month in QlikView

Count if Set Analytics Dual Criteria for Records closed in current month

leale1997 — Wed, 25 Nov 2020 16:16:04 GMT

Hi, I am trying to create a formula that will count specific records in a data set.

The records counted should meet the following criteria:

- [DateClosed] is within the current month.

- [Project] is = 'Yellow' or 'Green' or 'Blue'

This is the formula I have but, for some reason I don't understand, its counting every record using the first [DateClosed] criteria but not applying the [Project] criteria:

=num(Count({<DateClosed = {"$(='>=' & Date(MonthStart(Today())) & '<=' & Date(MonthEnd(Today())))"},

project = {'Yellow', 'Green','Blue'}>} RecordID),'#,##0', '.' , ',' )

Re: Count if Set Analytics Dual Criteria for Records closed in current month

avkeep01 — Wed, 24 Oct 2018 15:01:32 GMT

Hi Erika,

Just to be sure: check if it should be project or Project. you wrote them in different ways.

Re: Count if Set Analytics Dual Criteria for Records closed in current month

leale1997 — Wed, 24 Oct 2018 15:10:48 GMT

Ah Ha!!!

And so the question is answered. Thirty minutes beating my head against the desk because I left out the capital letter 'P'. Who knew??

Thanks so much for being my second set of eyes on this...

If I wanted to add a third criteria, would something like this work:

=num(Count({<DateClosed = {"$(='>=' & Date(MonthStart(Today())) & '<=' & Date(MonthEnd(Today())))"},

Project = {'Yellow', 'Green','Blue'}>}, Division = {'Americas', 'UK', 'Asia'} RecordID),'#,##0', '.' , ',' )

Re: Count if Set Analytics Dual Criteria for Records closed in current month

avkeep01 — Wed, 24 Oct 2018 15:28:47 GMT

Almost only the <] should be after the division part instead of the project part.

Re: Count if Set Analytics Dual Criteria for Records closed in current month

leale1997 — Wed, 24 Oct 2018 15:37:41 GMT

Got it! That's working perfectly. Thanks!