topic Set modifier question - filter for Year not working in QlikView

Set modifier question - filter for Year not working

Thu, 16 Jan 2014 19:10:09 GMT

I've spent much time searching for what I think is an easy answer, but I haven't yet found the solution.

I am trying to do the simplest of formulas...I created a variable vOpptyCreatedYr = Year(OpptyCreatedDate). Now I'm trying to create an expression that isn't working: COUNT (DISTINCT {vOpptyCreatedYr={2013})>} DART__ID), and while it says the expression is Ok, it is still not filtering for 2013 - there is an error/red error indicator beneath the open bracket just before the "2".

I think this is simple but I can't find what I'm missing. Thanks in advance for your help! -Dan

Re: Set modifier question - filter for Year not working

Nicole-Smith — Thu, 16 Jan 2014 19:14:28 GMT

An option without the variable (because you can't use a variable like that in set analysis):

COUNT ({<OpptyCreatedDate={'>=$(=makedate(2013))<=$(=yearend(makedate(2013)))'}>}DISTINCT DART__ID)

Or you need to create a OpptyCreatedYear field in your load script, and then you can:

COUNT ({<OpptyCreatedYear={2013}>}DISTINCT DART__ID)

Re: Set modifier question - filter for Year not working

preminqlik — Thu, 16 Jan 2014 19:18:19 GMT

you do one thing create

year field at backend and

in front end use

Count({<Year={'2013'}>} distinct DART__ID)

Re: Set modifier question - filter for Year not working

rustyfishbones — Thu, 16 Jan 2014 19:53:40 GMT

Create Year in the Script

Year (OpptyCreatedDate) AS Year

And the use the following expression

Count ({<Year ={'2013'}>} DISTINCT DART_ID)

Re: Set modifier question - filter for Year not working

Clever_Anjos — Thu, 16 Jan 2014 19:55:57 GMT

you forgot <>

COUNT (DISTINCT {<vOpptyCreatedYr={2013}>} DART__ID)

Re: Set modifier question - filter for Year not working

Thu, 16 Jan 2014 20:26:54 GMT

I like the idea of creating the year variable in the script...I'm just not sure where. I can't do it in the SQL SELECT statment...so how do I get it in the table?

Re: Set modifier question - filter for Year not working

Nicole-Smith — Thu, 16 Jan 2014 20:28:54 GMT

YourTable:

LOAD *,

Year(OpptyCreatedDate) as OpptyCreatedYear;

SQL SELECT

whatever your stuff is here

So to summarize, you can do a preload to add the year as a field in addition to all of your other fields.

Re: Set modifier question - filter for Year not working

preminqlik — Thu, 16 Jan 2014 20:34:00 GMT

hi try this

COUNT (DISTINCT if($(vOpptyCreatedYr)=2013, DART__ID))

Re: Set modifier question - filter for Year not working

Fri, 31 Jan 2014 00:26:12 GMT

Thanks for the replies. In this case, I had 4 other filters to use in the expression, so I just used an COUNT(IF) statement.