Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
SJ_16
Contributor II
Contributor II

Date Functions

Hi,

I have a date variable (Date_In) formatted YYYY-MM-DD. 

I need some logic please to calculate YTD as currently I hard code it in like below:

DATE_IN = {"2018*"}

It would be useful to have another calculation for previous year too please as I do the same as above:

DATE_IN = {"2017*"} 

Thanks

6 Replies
avkeep01
Partner - Specialist
Partner - Specialist

Hi , 

you could try:

DATE_IN{">=2018-01-01 <=2018-12-31"} for example. 

Same for 2017. 

avkeep01
Partner - Specialist
Partner - Specialist

And you can make the date fields dynamic if you want. Like:

DATE_IN(">=$(=MIN(DATE_IN)) <=$(=MAX(DATE_IN))"}

Gabriel
Partner - Specialist III
Partner - Specialist III

Hi,

 

Try YEARTODATE function in 

SJ_16
Contributor II
Contributor II
Author

Thanks for your suggestions but I can't quite get it to work, this is the full code below, any ideas please?

RangeSum(Above(Sum({<[Country] = {'UK'}>}if(Left(DATE_IN,4)='2017'
and Lookup='Renewal_Value',Val)),0, RowNo()))
/
RangeSum(Above(Sum({<[Country] = {'UK'}>}if(Left(DATE_IN,4)='2017'
and Lookup='due for renewal' ,Val)),0, RowNo()))
SJ_16
Contributor II
Contributor II
Author

Anyone able to assist with the above logic please?
ggijben
Partner - Creator II
Partner - Creator II

You should make a flag in your calendar script:

InYearToDate(<DateField>, Today(), 0) * -1   AS CYTDFlag
InYearToDate(<DateField>, Today(), -1) * -1  AS LYTDFlag

 

Then use the flag in your set analysis:

SUM({$< CYTDFlag = {1}>}  <Field> )