1 Reply Latest reply: Sep 14, 2018 2:07 PM by Andrey Krylov RSS

    Comparison between date range using variables cross years

    Marcello Castoro

      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?