Solved: Year Selection from Two Different Dates - Qlik Community

sunil-kumar5 · ‎2021-02-18

Hello,

I have a below table where sale of LicenseNo was made on installation date and it will expired on due date. This is a dummy data however I would have previous years data as well.

InstallationDate	DueDate	LicenseNo
01-09-2020	01-09-2021	240885
02-09-2019	01-09-2020	241867
03-05-2019	02-05-2020	250459
04-09-2020	04-09-2021	246757
05-10-2019	04-10-2020	247100
06-07-2020	06-07-2021	253478
07-09-2020	07-09-2021	251131
08-12-2020	08-12-2021	252723
22-11-2020	22-11-2021	241428
10-09-2020	10-09-2021	251011
11-09-2020	11-09-2021	244520
12-02-2019	12-02-2020	247193
13-09-2020	13-09-2021	237246
14-09-2020	14-09-2021	242684

I actually want to get the data like count(licenseno) but in the pivot table there will be year and month which would required year and month from both the dates.

Installation count from installation year as when the license installed.

Expired count from due date as when the license was expired or would be.

In each year month columns should have both the values based on one year and month which is actually coming from installation date and expired date.

Year

Jan

feb

mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

2015

installation-expired

2016

installation-expired

2017

installation-expired

2018

installation-expired

2019

installation-expired

2020

installation-expired

2021

installation-expired

QFabian · ‎2021-02-18

Hi @sunil-kumar5 , please try this script :

DataX:
LOAD * INLINE [
InstallationDate, DueDate, LicenseNo
01-09-2020, 01-09-2021, 240885
02-09-2019, 01-09-2020, 241867
03-05-2019, 02-05-2020, 250459
04-09-2020, 04-09-2021, 246757
05-10-2019, 04-10-2020, 247100
06-07-2020, 06-07-2021, 253478
07-09-2020, 07-09-2021, 251131
08-12-2020, 08-12-2021, 252723
22-11-2020, 22-11-2021, 241428
10-09-2020, 10-09-2021, 251011
11-09-2020, 11-09-2021, 244520
12-02-2019, 12-02-2020, 247193
13-09-2020, 13-09-2021, 237246
14-09-2020, 14-09-2021, 242684
];

Data:
Load
LicenseNo,
'Installation' as Type,
year(InstallationDate) as Year,
month(InstallationDate) as Month
Resident DataX;

Load
LicenseNo,
'Expiration' as Type,
year(DueDate) as Year,
month(DueDate) as Month
Resident DataX;

Then you can create a pivot table like this :

Please let my know if this is an acceptable solution for you!

QFabian

View solution in original post

QFabian · ‎2021-02-22

Ok!, let's do some changes in chart :

1.-take off 'type' dimension

2.- replace first expression with : Count({<Type = {'Installation'}>} LicenseNo)

3.- add a new second expression : Count({<Type = {'Expiration'}>} LicenseNo)

4.- add a final expression : column(1) / column(2)

5.- move "Values" to the left

😉

QFabian

View solution in original post

QFabian · ‎2021-02-18

Hi @sunil-kumar5 , please try this script :

DataX:
LOAD * INLINE [
InstallationDate, DueDate, LicenseNo
01-09-2020, 01-09-2021, 240885
02-09-2019, 01-09-2020, 241867
03-05-2019, 02-05-2020, 250459
04-09-2020, 04-09-2021, 246757
05-10-2019, 04-10-2020, 247100
06-07-2020, 06-07-2021, 253478
07-09-2020, 07-09-2021, 251131
08-12-2020, 08-12-2021, 252723
22-11-2020, 22-11-2021, 241428
10-09-2020, 10-09-2021, 251011
11-09-2020, 11-09-2021, 244520
12-02-2019, 12-02-2020, 247193
13-09-2020, 13-09-2021, 237246
14-09-2020, 14-09-2021, 242684
];

Data:
Load
LicenseNo,
'Installation' as Type,
year(InstallationDate) as Year,
month(InstallationDate) as Month
Resident DataX;

Load
LicenseNo,
'Expiration' as Type,
year(DueDate) as Year,
month(DueDate) as Month
Resident DataX;

Then you can create a pivot table like this :

Please let my know if this is an acceptable solution for you!

QFabian

sunil-kumar5 · ‎2021-02-19

Hi QFabian,

Can you please advise as which year and month have you taken from installation or duedate?

Thanks

sunil-kumar5 · ‎2021-02-22

Thanks Got it..

sunil-kumar5 · ‎2021-02-22

Though its accepted as solution but I just wanted to add one more field that is Expired%

like (expiration/installation)*100

how would it be possible?

Please advise..

QFabian · ‎2021-02-22

Ok!, let's do some changes in chart :

1.-take off 'type' dimension

2.- replace first expression with : Count({<Type = {'Installation'}>} LicenseNo)

3.- add a new second expression : Count({<Type = {'Expiration'}>} LicenseNo)

4.- add a final expression : column(1) / column(2)

5.- move "Values" to the left

😉

QFabian

sunil-kumar5 · ‎2021-02-23

Hi Fabian,

Its working fine, thank you so much for your help..

🙂