Countif based on date ranges

Daniel_K — Fri, 22 Nov 2019 16:05:54 GMT

Hello,

I've got a dataset of accounts which should be billed on either a monthly or quarterly basis (M/Q) and I need to establish if they're billed up to date or not up to date and then summarise the results to show the number and % not up to date and up to date regardless of the billing frequency.

We consider a monthly billed account up to date if the latest billed date is less than 46 days since todays date.

We consider a quarterly billed account up to date if the latest billed date is less than 155 days since todays date.

The attached dataset contains 25 lines (excluding the header line) but I only want the formula to bring back one instance of the account so I know the expected results are

20 individual accounts

14 up to date (70%)

6 not up to date (30%)

I just can't get the expression to work correctly!

Thank you in advance for your help

Re: Countif based on date ranges

Nicole-Smith — Fri, 22 Nov 2019 17:37:10 GMT

20 individual accounts:
count(distinct Account)

14 up-to-date accounts:
count({<[Billing Frequency]={'M'}, [Latest Billed Date]={">$(=today()-46)"}>}distinct Account) +
count({<[Billing Frequency]={'Q'}, [Latest Billed Date]={">$(=today()-155)"}>}distinct Account)

6 not up-to-date accounts:
count(distinct Account) -
(count({<[Billing Frequency]={'M'}, [Latest Billed Date]={">$(=today()-46)"}>}distinct Account) +
count({<[Billing Frequency]={'Q'}, [Latest Billed Date]={">$(=today()-155)"}>}distinct Account))

topic Re: Countif based on date ranges in QlikView

Countif based on date ranges

Re: Countif based on date ranges