topic Re: Set Analysis involving date in QlikView

Set Analysis involving date

qlikhereqlikthe — Sat, 16 Nov 2024 04:28:13 GMT

Dear all,

I face difficulty in summing up the total claim paid out for one of my client. The situation as below:

1. Client ABC has been our client for years, but we would like to know the total claim paid out throughout their latest in force period which is in this case, 1st July 2018 to 30th June 2019. Coverage year is 2018 as the year of the commencement is 2018.

I've tried the expression, but both expressions failed to give me the total claim paid for each admission:

a. sum({<[Date of Admission]={'>=PolicyCommerce<=PolicyRenew'}>}[Total Payable])

b.sum({<[Date of Admission]={"[Date of Admission]>=PolicyCommerce, [Date of Admission]<=PolicyRenew"}>}[Total Payable])

Client	PolicyNumbers	PolicyCommence	PolicyRenew	Coverage_Year	Date of Admission	Claim Payable
ABC	P1234	01/07/2018	30/06/2019	2018	14/07/2018	RM0.00
ABC	P1234	01/07/2018	30/06/2019	2018	31/08/2018	RM0.00
ABC	P1234	01/07/2018	30/06/2019	2018	06/09/2018	RM0.00
ABC	P1234	01/07/2018	30/06/2019	2018	08/10/2018	RM0.00

Hence, anyone can provide their advice on how to tackle this issue?

Re: Set Analysis involving date

swapniltaz — Wed, 13 Feb 2019 06:22:37 GMT

Hi ,

Can you Use Below

sum({<[Date of Admission]={">=PolicyCommerce <=PolicyRenew"}>}[Total Payable])

Re: Set Analysis involving date

qlikhereqlikthe — Wed, 13 Feb 2019 06:31:37 GMT

Dear Swapniltaz,

I've tried, it comes out as 0 too. 😞

Re: Set Analysis involving date

PrashantSangle — Wed, 13 Feb 2019 06:37:07 GMT

Hi,

can you share some sample data with expected output??

Regards,
Prashant Sangle

Re: Set Analysis involving date

swapniltaz — Wed, 13 Feb 2019 06:53:54 GMT

I forsee Date format issue or Qlik is not able to read and compaare your dates properly

PLease Use following variable in your Qlik formula

vPolicyRenew: Date(Date#(PolicyRenew,'DD/MM/YYYY' )'DD/MM/YYYY')
vPolicyCommerce: Date(Date#(PolicyCommerce,'DD/MM/YYYY' )'DD/MM/YYYY')
vDA : Date(Date#([Date of Admission],'DD/MM/YYYY' )'DD/MM/YYYY')

Re: Set Analysis involving date

qlikhereqlikthe — Wed, 13 Feb 2019 11:52:08 GMT

Hey there, this is just a simple sample for your perusal

Re: Set Analysis involving date

qlikhereqlikthe — Wed, 13 Feb 2019 11:53:12 GMT

I have up to 10,000++ client, do I have to create the variables for all of them? As some of them would have different policy commencement date.