topic Filter Contract Expiry Date in QlikView

Filter Contract Expiry Date

PRS — Wed, 25 Nov 2020 16:16:04 GMT

Hi Folks,

I am facing some issues while filtering Contract Expiry date.

i have a date field 'Contract Expiry Date' and i am comparing this field with today to know to know in how many days the contract is going to expire.

So, i have created a multi box with filters like 'Expired','30 Days','60 Days','90 Days','>90 Days' using below expression.

=if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=0,'Expired',

if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=30,'30 Days',

if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=60,'60 Days',

if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=90,'90 Days',

if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())>90,'>90 Days'

)))))

When i am selecting 'Expired' or '30 days', it is showing correct result.

But when i select 60 days, it is showing dates which are >30 and <=60.

But the actual result should be, it should show all dates which are expiring with in 60 days. (i.e;from 1 to 60 days).

Same issue is existing for rest of filters(60 days and >90 Days)

I am attaching QVW for your reference.

Could anybody do needful and give me suggestions.

Thanks,

Rajasekha

Re: Filter Contract Expiry Date

rupamjyotidas — Tue, 29 Nov 2016 18:30:23 GMT

If else won't work in this condition as once conditions are met in first condition it won't be included after that

For Eg: if(a<30,'30',if(a<60,'60')) , '60' won't include below 30 values

You might have to create individual fields for the same in your script

if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=0 as 'Expired',

if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=30 as '30 Days',

Etc

Re: Filter Contract Expiry Date

MK_QSL — Tue, 29 Nov 2016 18:37:25 GMT

Please don't create multiple thread for same question..

Thanks

Re: Filter Contract Expiry Date

PRS — Tue, 29 Nov 2016 18:38:32 GMT

Hi Rupam Das,

Thanks for reply,

If i create individual fields for each filter from script, then could you please tell me how to show all filters('Expired',30 days,60 days etc) in a single multi box.?

Re: Filter Contract Expiry Date

stabben23 — Tue, 29 Nov 2016 19:04:43 GMT

Hi,

you need to have more and less than, ex if you got an expired 29, it will be less than 30 and less than 60 and less than 90.

Re: Filter Contract Expiry Date

rupamjyotidas — Tue, 29 Nov 2016 19:07:50 GMT

Manish below has given you the solution.

Re: Filter Contract Expiry Date

Tue, 29 Nov 2016 19:20:12 GMT

Hi Rajasekhar,

For this you need to use resident load concept with extra Boolean fields.

TEMP:

LOAD

if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=0,1,0) AS EXPIRED_FIELD,

if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=30,1,0) AS THIRTY_DAYS_FIELD ,

if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=60,1,0) AS SIXTY_DAYS_FIELD ,

if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=90,1,0) AS NINTY_DAYS_FIELD ,

if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())>90,1,0) AS GREATERTHAN_NINTY_DAYS_FIELD

FROM TABLE;

TEMP:

LOAD

IF(EXPIRED_FIELD=1, 'EXPIRED',

IF( THIRTY_DAYS_FIELD=1 AND SIXTY_DAYS_FIELD=1 AND NINTY_DAYS_FIELD=1 AND GREATERTHAN_NINTY_DAYS_FIELD=1,'>90 Days',

IF( THIRTY_DAYS_FIELD=1 AND SIXTY_DAYS_FIELD=1 AND NINTY_DAYS_FIELD=1

AND GREATERTHAN_NINTY_DAYS_FIELD=0,'90 Days',

IF( THIRTY_DAYS_FIELD=1 AND SIXTY_DAYS_FIELD=1 AND NINTY_DAYS_FIELD=0

AND GREATERTHAN_NINTY_DAYS_FIELD=0,'60 Days',

IF( THIRTY_DAYS_FIELD=1 AND SIXTY_DAYS_FIELD=0 AND NINTY_DAYS_FIELD=0

AND GREATERTHAN_NINTY_DAYS_FIELD=0,'30 Days') AS EXPIRY_FIELD

RESIDENT TEMP;

DROP TABLE TEMP;

-ASHOK

Re: Filter Contract Expiry Date

PRS — Wed, 30 Nov 2016 06:23:07 GMT

Hi,

Thank you very much for quick reply. It's working.

And i have another scenario.

When i am adding Contract expiry field in a table where there are already some fields, then it is creating concatenation and giving extra rows.

Is there any way to get rid of this?

I attached the QVW file for your reference.

Regards,

Rajasekhar

Re: Filter Contract Expiry Date

MK_QSL — Wed, 30 Nov 2016 07:00:43 GMT

Close this thread by selecting correct answer if you found your solution and create a new thread.

Mixing two questions in one thread is not a good idea.