Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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))