Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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 ?
Why would it not work when it is compared to Today()?
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
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.