Load Script Where Condition not working

[siva_boggarapu]

Hi Team,

I am trying to load the below script but not working Properly. My YearMonth='202012' format . Please correct me the script what is wrong here?

Load CustomerID&ProductID&SalesEntityCode as LinkKey,
CustomerID,
ProductID,
SalesEntityCode,
YearMonth,
Sum(TotalSales9LQty) as SumofLYActuals
resident depletions
where
YearMonth> text(date(addmonths(today(),-17),'YYYYMM'))
and YearMonth <= text(date(addmonths(today(),-14),'YYYYMM'))
Group BY CustomerID&ProductID&SalesEntityCode,CustomerID, ProductID,SalesEntityCode,YearMonth;

Regads,

Siva

[Vegar]

It could be the text that you are trying to compare to an number . What happens if you put num around your text() like this?

Load CustomerID&ProductID&SalesEntityCode as LinkKey,
CustomerID,
ProductID,
SalesEntityCode,
YearMonth,
Sum(TotalSales9LQty) as SumofLYActuals
resident depletions
where
YearMonth> NUM(text(date(addmonths(today(),-17),'YYYYMM')))
and YearMonth <= num(text(date(addmonths(today(),-14),'YYYYMM')))
Group BY CustomerID&ProductID&SalesEntityCode,CustomerID, ProductID,SalesEntityCode,YearMonth;

[siva_boggarapu]

My only target I need to put where condition load previous 17 and 14 month old data from Today() date.

with Compared to YearMonth='201911' like that. Please tell me how I can get it?

[MayilVahanan]

Hi @siva_boggarapu 

Try like below

where
Floor(MakeDate(Left(YearMonth,4), Right(YearMonth,2))) >= Floor(Monthstart(today(),-17))
and Floor(MakeDate(Left(YearMonth,4), Right(YearMonth,2))) <= Floor(MonthEnd(today(),-14));