Retrieving month from date value within set analysis expression

[#1]

Hi all,

I'm new to Qlik Sense Desktop, and am having what seems to be a relatively simple problem. I have two data fields, BOL and Finish_DateTime. I'm trying to create a series of gauges that show the number of distinct BOL values broken out by month. My expression to retrieve a count of all distinct BOL values is:

COUNT(DISTINCT(BOL))

This is relatively straightforward. The problem I'm encountering is that I'd like to filter based upon the month of the Finish_DateTime value; something similar to:

COUNT({$<MONTH(Finish_DateTime)={1}>}DISTINCT(BOL))

I'd like this expression to work for January, and will then create similar gauges for the other months. I'm just having trouble figuring out the proper syntax to say, "Hey, only give me records where the month is 1 (or whichever month I need)." Any advice on the matter would be greatly appreciated.

[sunny_talwar]

May be this:

Count({$<Finish_DateTime={"=Num(Month(Finish_DateTime)) = 1"}>}DISTINCT BOL)

[jonathandienst · Accepted solution]

The LHS of a set expression must be field name. If you are going to be using month as a dimension I would add a Month derived field during the LOAD:

     LOAD Finish_DateTime,

          Month(Finish_DateTime) As FinishMonth,

Now you can use FinishMonth in your set expression. This is the best solution.

Otherwise you could try

     COUNT({$<Finish_DateTime={"=$(Month(Finish_DateTime) = 'Jan')"}>} DISTINCT BOL)

     (May need to play with the search text to get that to work)

[qlikviewwizard]

Hi Try like this in Expression:

Count({$<Finish_DateTime={"=Num(Month(Finish_DateTime)) = 1"}>}Distinct BOL)