Set Analysis involving date

[qlikhereqlikthe]

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?

[swapniltaz]

Hi ,

Can you Use Below 

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

[qlikhereqlikthe]

Dear Swapniltaz,

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

[PrashantSangle]

Hi,

can you share some sample data with expected output??

Regards,
Prashant Sangle

[swapniltaz]

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')

[qlikhereqlikthe]

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

[qlikhereqlikthe]

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.