Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
mlamb2
Contributor
Contributor

Filtering by year in set analysis using Or and Distinct

Hello, I would like to filter out this data by year using the set expression below. However, when I add year to the set analysis, it returns a value of zero. 

Here is the set analysis as it stands. This correctly generates numerical values. 

count(distinct(if([CONTRIB_CODE-CONTRIB_CODE1]='07' or [CONTRIB_CODE-CONTRIB_CODE1]='19' or[CONTRIB_CODE-CONTRIB_CODE1]='60.88'
or CONTRIB_CODE2= '07' or CONTRIB_CODE2='19' or CONTRIB_CODE2='60.88' or CONTRIB_CODE3='07'or CONTRIB_CODE3= '19' or CONTRIB_CODE3='60.88'
or DISTRACTED_BY_CODE='01' or DISTRACTED_BY_CODE='02' or DISTRACTED_BY_CODE= '03' or DISTRACTED_BY_CODE= '04' or DISTRACTED_BY_CODE= '05' or
DISTRACTED_BY_CODE='06'or DISTRACTED_BY_CODE= '07' or DISTRACTED_BY_CODE= '09' or DISTRACTED_BY_CODE='10'
or DISTRACTED_BY_CODE= '12' or DISTRACTED_BY_CODE='13' or DISTRACTED_BY_CODE='14' or DISTRACTED_BY_CODE='15'
or DISTRACTED_BY_CODE='16'or DISTRACTED_BY_CODE= '88' or DISTRACTED_BY_CODE='18'or DISTRACTED_BY_CODE= '17',OVERALL_REPORT_NO)))

 

Here is the set analysis where I attempted to further filter the data by date. It consistently returns 0 as a value. When I use the set analysis as a measure and year as a dimension however, it returns values for 2015. So I know that the answer is not actually 0. 

count(distinct(if([ACC_DATE.autoCalendar.Year]='2015',[ACC_DATE.autoCalendar.Year] and(if([CONTRIB_CODE-CONTRIB_CODE1]='07' or [CONTRIB_CODE-CONTRIB_CODE1]='19' or[CONTRIB_CODE-CONTRIB_CODE1]='60.88'
or CONTRIB_CODE2= '07' or CONTRIB_CODE2='19' or CONTRIB_CODE2='60.88' or CONTRIB_CODE3='07'or CONTRIB_CODE3= '19' or CONTRIB_CODE3='60.88'
or DISTRACTED_BY_CODE='01' or DISTRACTED_BY_CODE='02' or DISTRACTED_BY_CODE= '03' or DISTRACTED_BY_CODE= '04' or DISTRACTED_BY_CODE= '05' or
DISTRACTED_BY_CODE='06'or DISTRACTED_BY_CODE= '07' or DISTRACTED_BY_CODE= '09' or DISTRACTED_BY_CODE='10'
or DISTRACTED_BY_CODE= '12' or DISTRACTED_BY_CODE='13' or DISTRACTED_BY_CODE='14' or DISTRACTED_BY_CODE='15'
or DISTRACTED_BY_CODE='16'or DISTRACTED_BY_CODE= '88' or DISTRACTED_BY_CODE='18'or DISTRACTED_BY_CODE= '17',OVERALL_REPORT_NO)))))

If anybody can provide any insight on how to solve this I would greatly appreciate it!

In addition, I am new to QLIK and largely self taught so new information or pointers are always welcome!

1 Reply
jwjackso
Specialist II
Specialist II

Count(distinct(if([ACC_DATE.autoCalendar.Year]=2015,

I think the problem may be that you are comparing Year to a string instead of a number.