Hello nachofernandez,
Whenever I try to do set analysis with dates, I always convert them to numerical values first. As we are performing logical operations (>= <=), it is best to work with integers rather than date formats that may cause you some nightmares.
Find below my notes.
Script:
LOAD * INLINE [
id, SalesDate, SalesDateNum, Sales
1, 01/01/2018, 43101, 0
1, 01/01/2018, 43132, 10000
1, 01/01/2018, 43160, 40000
1, 01/01/2018, 43191, 50000
];
Let Start_Date = Num(Date(MakeDate(2018, 1, 1)))
Let End_Date = Num(AddMonths($(Start_Date), 2))
Set Analysis (see the flow):
SUM( {<[SalesDateNum]={">= 43101 <= 43160"}>} [Sales]) --> 50k
SUM( {<[SalesDateNum]={">= 43101 <= $(End_Date)"}>} [Sales])--> 50k
SUM( {<[SalesDateNum]={">= 43101 <= $(=Num(AddMonths($(Start_Date), 2)))"}>} [Sales]) --> 50k (Notce $=(..) )
SUM( {<[SalesDateNum]={">= 43101 <= Num(AddMonths($(Start_Date), 2)))"}>} [Sales]) --> 100k
Most probable, your error was due to the $(= ..) variable inside your SalesDate expression:
Not working: Sum({$<SalesDate={">=$(Date(Start_Date))<= $(AddMonths(Date(Start_Date),2))"}>}Sales)
Working: Sum({<[SalesDateNum]={">= 43101 <= $(=Num(AddMonths($(Start_Date), 2)))"}>} [Sales])
You could replicate these numbers using your SalesDate in date format. But be aware that even though you type in 20180101, this may not be a numerical value but a date value. To prevent such discrepancies, I highly recommend you to use numerical values always.
Hope it helps 🙂
Saludos,
