Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, someone please help me to create Aging by month instead of days (Ex: NumberofMonth >1, NumebrofMonth >12, NumebrofMonth >24 and NumebrofMonth >=48 .............) like this
load *, if( Numberofdays<=31 or Numberofdays<=30 or Numberofdays<=28 or Numberofdays<=29, Dual('New Month',1),
if( Numberofdays >31 and (Numberofdays<=365 or Numberofdays<=366), Dual('1 Year',2),
if( Numberofdays >365 and (Numberofdays<=732 or Numberofdays<=731), Dual('2-3 Year',3),
if( Numberofdays >1460 and (Numberofdays<=1825 ), Dual('4-5 Year',4),Dual('>5 Years',5)
)))) as Bucket;
load *, Today()-[sales date] as Numberofdays;
LOAD [Cust ID],
[Sales ITM],
[sales date]
FROM
(ooxml, embedded labels, table is Sheet1);
You can try using NumberOfMonth as created below
load *, if( Numberofdays<=31 or Numberofdays<=30 or Numberofdays<=28 or Numberofdays<=29, Dual('New Month',1),
if( Numberofdays >31 and (Numberofdays<=365 or Numberofdays<=366), Dual('1 Year',2),
if( Numberofdays >365 and (Numberofdays<=732 or Numberofdays<=731), Dual('2-3 Year',3),
if( Numberofdays >1460 and (Numberofdays<=1825 ), Dual('4-5 Year',4),Dual('>5 Years',5)
)))) as Bucket;
load *,
(Year(Today()) - Year([sales date]))*12 - (Month(Today()) - Month([sales date])) as NumberOfMonth,
Today()-[sales date] as Numberofdays,
;
LOAD [Cust ID],
[Sales ITM],
[sales date]
FROM
(ooxml, embedded labels, table is Sheet1);