Distinct Count for Rolling of Previous Year (365 Days) for Qtr and Month

[sjhussain]

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.

[maxgro]

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 ?

[sjhussain]

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?

[maxgro]

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.

[pradosh_thakur]

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