Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
nezuko_kamado
Contributor III
Contributor III

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

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

nezuko_kamado_0-1622788983907.png

 

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
];

Labels (3)
1 Solution

Accepted Solutions
MayilVahanan

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:

MayilVahanan_0-1622854763115.png

 

Thanks & Regards,
Mayil Vahanan R

View solution in original post

3 Replies
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'

Thanks & Regards,
Mayil Vahanan R
nezuko_kamado
Contributor III
Contributor III
Author

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

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:

MayilVahanan_0-1622854763115.png

 

Thanks & Regards,
Mayil Vahanan R