Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
We are working on a project in the insurance industry. We want to count the number of beneficiaries from the transaction log who have been active for the past one year.
In the transaction log we have the BEN_ID (Benificiary ID) and DATE when the beneficiary was added. We are using the following set analysis to calculate the distinct count of beneficiaries on date selection
Count({<BTL_DATE={'>=$(=Date(Max(BTL_DATE)-365)) <=$(=Date(Max(BTL_DATE)))'},BTL_TYPE={'1','7','8'},B_ISCANCELED={'0'}>}Distinct BEN_ID)
This gives the count of Beneficiaries who have been active in the past one year.
We are having problems when we select Quarter and Month.
For example
BEN_ID, BTL_DATE, BTL_TYPE
1, 1/1/2017, 1
2, 3/4/2017, 1
3, 1/1/2016, 1
4, 2/4/2016, 1
5, 3/3/2016, 1
If we do not select a date, it will give the count of distinct beneficiaries on today - BEN_ID 1&2. If we select 3/5/2016 then it will give BEN_ID 3,4,5 because they fall within one year of 3/5/2016.
The problem we are having is getting distinct count on Quarter and Month.
If we select 2017-Qtr1 then it should select each date from Jan 01, 2017 to March 31, 2017 and go back one year for each date and get the distinct beneficiaries. Same goes for the Month. This is where we are having the problem.
Will appreciate if someone can assist.
Thanks.
If we select 2017-Qtr1 then it should select each date from Jan 01, 2017 to March 31, 2017 and go back one year for each date and get the distinct beneficiaries. Same goes for the Month. This is where we are having the problem.
Do you mean you want to count the distinct beneficiaries from Jan 1, 2016 to March 31, 2017 ?
Massimo,
Thanks for your response. I did not think of it this way. Expanding on your question on my question you are right this is what we want to do so we can have it like the following (please correct me)
original:
BTL_DATE={'>=$(=Date(Max(BTL_DATE)-365)) <=$(=Date(Max(BTL_DATE)))
new:
BTL_DATE={'>=$(=Date(Min(BTL_DATE)-365)) <=$(=Date(Max(BTL_DATE)))
It would be greater than the Min(BTL_DATE)-365
So this way what ever we select whether single date or Month or Quarter we can just do Min(BTL_DATE)-365 and it will give the distinct counts.
Is this right?
Yes I thought this.
I can't think of another interpretation, but maybe you have to check with the user if this undertanding is correct.
hi
why not we try this once
if(getselectedcount(QUARTER_FIELD) >0 or getselectedcount(MONTH_FIELD) >0 , Count({<BTL_DATE={'>=$(=Date(Min(BTL_DATE)-365)) <=$(=Date(Max(BTL_DATE)))'},BTL_TYPE={'1','7','8'},B_ISCANCELED={'0'}>}Distinct BEN_ID),Count({<BTL_DATE={'>=$(=Date(Max(BTL_DATE)-365)) <=$(=Date(Max(BTL_DATE)))'},BTL_TYPE={'1','7','8'},B_ISCANCELED={'0'}>}Distinct BEN_ID))
and i will recommend you to read this once so that you wont face problems in future for using single quotes.
https://community.qlik.com/blogs/qlikviewdesignblog/2017/09/11/quotes-in-set-analysis
regards
Pradosh