Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

New Contributor

Comparison between date range using variables cross years


i'm trying to calculate the numbers of customers between two dates ( two dates of one year) and compare the same period in the past years.

Using the following statement I have the number of customers between the two dates but not in the others years.

Count({$<[DataDoc.autoCalendar.Date]={">=$(=Date(StartDate))<=$(=Date(EndDate))"}>} distinct [CustomerCode])

I tried this:

Count({$<[DataDoc.autoCalendar.Date]={">=$(=Date(StartDate))<=$(=Date(EndDate))"}>+1<[DataDoc.autoCalendar.Year]=>} distinct [CustomerCode])

but it doesn't work because it return all dates.

I could use the function AddYears() but I don't wont write n times the same statement...

Count({$<[DataDoc.autoCalendar.Date]={">=$(=AddYears(Date(StartDate)),-1)<=$(=AddYears(Date(EndDate)),-1)"}>} distinct [CustomerCode])


Count({$<[DataDoc.autoCalendar.Date]={">=$(=AddYears(Date(StartDate)),-2)<=$(=AddYears(Date(EndDate)),-2)"}>} distinct [CustomerCode])

and so on....

I'm sure it's more easy....any suggest?

1 Reply
Valued Contributor

Re: Comparison between date range using variables cross years

Hi, Marcello. You can add a field to the calendar like this

Num(Month(Date)) & Num(Day(Date), '00')  as [MonthDay]

and the statement would be

Count({$<[[MonthDay] ={">=$(=Date(StartDate, 'MDD'))<=$(=Date(EndDate, 'MDD'))"}, [DataDoc.autoCalendar.Date], [DataDoc.autoCalendar.Year]>} distinct [CustomerCode])