Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
curiousfellow
Creator II
Creator II

Stuck with quotion marks and variables in set analysis

I want to count some stuff per year based on a selected date (kind of YTD function)

I created this expression, but I am afraid there is something wrong with the syntax because I do not get any results.

I have a variable called "selected_date" . When I use today()  instead of the variable I get correct results.

My expression is :

=count(distinct{< employee_nr=
{"=floor(date(firstdate, 'DD_MM-YYYY')) <=
date(addyears('$(=$(selected_date))'),Year_registration- year('$(=$(selected_date))'),'DD-MM-YYYY')"
}>}
employee_nr)

I tried several options (double quote , single quotes , with or without dollar sign but nothing seems to be the correct combination.

 

 

 

1 Solution

Accepted Solutions
curiousfellow
Creator II
Creator II
Author

Solved :

count(distinct{< firstdate=
{"=DayNumberOfYear(firstdate)+

((num(year(firstdate))- Year_registration)*365)

<=DayNumberOfYear(selected_date)"}>} employee_nr)

Year_registration is sometimes larger then  year(firstdate) because year_registration is the year for which the registration is.

Did not know the function "DayNumberOfYear" before.

 

View solution in original post

5 Replies
sunny_talwar

Where exactly is selected_date is set with a value? A calendar object or input box? May be the date in the variable is read as text and you need to help it read as a date using date#() function

curiousfellow
Creator II
Creator II
Author

It is a slider/calendar object. When it would be read as a number, it would not work when comparing with today() either I think ?

sunny_talwar

Why would it not work when it is compared to Today()? 

curiousfellow
Creator II
Creator II
Author

I suppose today() will be recognized as a date ? So if the slider value would be seen as a number then it would result in the same wrong expression ?

At this moment I am trying date#  function by the way Smiley Happy 

curiousfellow
Creator II
Creator II
Author

Solved :

count(distinct{< firstdate=
{"=DayNumberOfYear(firstdate)+

((num(year(firstdate))- Year_registration)*365)

<=DayNumberOfYear(selected_date)"}>} employee_nr)

Year_registration is sometimes larger then  year(firstdate) because year_registration is the year for which the registration is.

Did not know the function "DayNumberOfYear" before.

 

View solution in original post