Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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])