Nested IF for Date#() in order to create seasonal levels

[nezuko_kamado]

I tried to create season levels of 'Fall', 'Winter', 'Spring' and 'Summer' from  own seasonal understanding.  But  it skipped the nested condition, and only categorized Fall(beginning) and Summer(end) without winter and spring

Where my code got wrong?

RIGHT JOIN(DateTable)
load
Id,

if( MonthYear > date#('Dec2020', 'MMMYYYY'), 'Fall20',
if( MonthYear <= date#('Dec2020', 'MMMYYYY')and MonthYear >date#('Feb2021', 'MMMYYYY'), 'Winter20',
if( MonthYear <= date#('Feb2021', 'MMMYYYY') and MonthYear >date#('May2021', 'MMMYYYY'), 'Spring21',
'Summer21'))) as Season Resident DateTable;

The table used is as below

DateTable:
load Id, workDate, station, date(workDate,'MM/DD/YYYY') as Date, Month(workDate) & Year(workDate) as MonthYear Inline [ Id, workDate, station
1, 11/12/2020, a
2, 11/15/2020, b
3, 11/25/2020, c
4, 11/29/2020, d
5, 12/1/2020,
6, 12/3/2020, a
7, 12/10/2020, c
8, 12/15/2020, a
9, 1/20/2021, a
10, 1/22/2021, b
11, 2/4/2021, a
12, 2/5/2021, b
13, 3/4/2021, a
14, 3/5/2021, a
15, 3/8/2021
16, 3/10/2021, b
17, 3/14/2021, b
18, 3/15/2021
19, 3/16/2021, c
20, 3/20/2021, a
21, 3/21/2021,c
22, 3/23/2021,c
23, 3/24/2021, c
24, 4/04/2021, a
25, 4/11/2021,a
26, 4/13/2021,b
27, 4/21/2021, c
28, 6/01/2021, c
];

[MayilVahanan]

Hi @nezuko_kamado 

Can you explain the range of ur season?

Because, ur first if condition is >= Dec 2020, so most of the dates satisfy this condition and display "Fall 2020".

if( MonthYear > date#('Dec2020', 'MMMYYYY'), 'Fall20'

[nezuko_kamado]

I kind of get your point and thank you so much, and the range of the season is below

Oct2020, Nov2020 => Fall

Dec2020, Jan2021=> Winter

Feb2021  ~  May2021=> Spring

Jun2021  ~ Sep2021=> Summer

After changing the code with closed range, still  doesn't work. The data has only one summer but Fall shows as summer.

RIGHT JOIN(DateTable)
load
Id,
if( MonthYear >= date#('Oct2020', 'MMMYYYY') and MonthYear <= date#('Nov2020', 'MMMYYYY'), 'Fall20',
if( MonthYear >= date#('Dec2020', 'MMMYYYY')and MonthYear <=date#('Jan2021', 'MMMYYYY'), 'Winter20',
if( MonthYear >= date#('Feb2021', 'MMMYYYY') and MonthYear <=date#('May2021', 'MMMYYYY'), 'Spring21',
'Summer21'))) as Season Resident DateTable;

help me~

[MayilVahanan · Accepted solution]

HI @nezuko_kamado 

Try like below

DateTable:
Load *,
if( MonthYear >= date#('Oct2020', 'MMMYYYY') and MonthYear <= date#('Nov2020', 'MMMYYYY'), 'Fall20',
if( MonthYear >= date#('Dec2020', 'MMMYYYY')and MonthYear <=date#('Jan2021', 'MMMYYYY'), 'Winter20',
if( MonthYear >= date#('Feb2021', 'MMMYYYY') and MonthYear <=date#('May2021', 'MMMYYYY'), 'Spring21',
'Summer21'))) as Season ;
load Id, workDate, station, date(workDate,'MM/DD/YYYY') as Date, Dual(Month(workDate) & Year(workDate),MonthName(workDate)) as MonthYear Inline [ Id, workDate, station
1, 11/12/2020, a
2, 11/15/2020, b
3, 11/25/2020, c
4, 11/29/2020, d
5, 12/1/2020,
6, 12/3/2020, a
7, 12/10/2020, c
8, 12/15/2020, a
9, 1/20/2021, a
10, 1/22/2021, b
11, 2/4/2021, a
12, 2/5/2021, b
13, 3/4/2021, a
14, 3/5/2021, a
15, 3/8/2021
16, 3/10/2021, b
17, 3/14/2021, b
18, 3/15/2021
19, 3/16/2021, c
20, 3/20/2021, a
21, 3/21/2021,c
22, 3/23/2021,c
23, 3/24/2021, c
24, 4/04/2021, a
25, 4/11/2021,a
26, 4/13/2021,b
27, 4/21/2021, c
28, 6/01/2021, c
];

O/P: