Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Basically I want to check if month of given dates by user is equal or less or greater than current month.
If its equal then " Current month Due", If its less then "Overdue", If its greater then "Ontime" .
When I apply simple formula month(Date) its does work, but it also give me wrong flag as its do not compare with years.
Please suggest.
If([Date_Field]= 'Closed','Closed',
If([Date_Field]= 'Cancelled','Cancelled',
If([Date_Field]= 'TBC','TBC',
If([Date_Field]= '','Blank',
If(Month([Date_Field])= Month(Today()),'Current Month Due',
If(Month([Date_Field])> Month(Today()),'Ontime',
If(Month([Date_Field])< Month(Today()),'Overdue'))))))) AS Flag
Hi Dipesh
Try this:
[Sample]:
LOAD
[Dates] AS [Dates],
If([Dates]= 'Closed','Closed',
If([Dates]= 'Cancelled','Cancelled',
If([Dates]= 'TBC','TBC',
If([Dates]= '','Blank',
If(Year([Dates])>Year(Today()),'Ontime',
If(Year([Dates])<Year(Today()),'Overdue',
If(Month([Dates])= Month(Today()),'Current Month Due',
If(Month([Dates])> Month(Today()),'Ontime',
If(Month([Dates])< Month(Today()),'Overdue'))))))))) AS Flag
FROM [lib://Downloads/Sample.xlsx]
(ooxml, embedded labels, table is Sample);
Or this if you just want to return the column name when it is not a date (or 'Blank' when it is empty):
[Sample]:
LOAD
[Dates] AS [Dates],
If([Dates]= '','Blank',
If(Year([Dates])>Year(Today()),'Ontime',
If(Year([Dates])<Year(Today()),'Overdue',
If(Month([Dates])= Month(Today()),'Current Month Due',
If(Month([Dates])> Month(Today()),'Ontime',
If(Month([Dates])< Month(Today()),'Overdue',[Dates])))))) AS Flag
FROM [lib://Downloads/Sample.xlsx]
(ooxml, embedded labels, table is Sample);
It basically checks for the year first and only compares the months if the year of the date is the same as the current year.
Regards,
Mauritz