Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
marcello_castor
Contributor
Contributor

Comparison between date range using variables cross years

Hello,

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
andrey_krylov
Specialist
Specialist

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])