I'm trying to create KPIs based on a selected range of dates, that counts needs to be specific based on the conditions that are filtering or arranging the records, so I'm using formulas to create the required distinct counts, you can take the following data sample as source
| Code |
purchase Date |
Last updated |
Status |
Last service updated |
term end |
| AA |
4/5/2022 |
3/10/2022 |
Light |
3/10/2022 |
4/8/2022 |
| AA |
4/5/2022 |
4/15/2022 |
Light |
4/13/2022 |
4/8/2022 |
| AA |
4/5/2022 |
4/20/2022 |
Full |
4/25/2022 |
5/10/2022 |
| AA |
4/5/2022 |
5/2/2022 |
Light |
5/2/2022 |
4/8/2022 |
| BB |
3/15/2022 |
4/15/2022 |
No |
3/10/2022 |
4/8/2022 |
| BB |
3/15/2022 |
4/20/2022 |
Light |
4/13/2022 |
4/8/2022 |
| BB |
3/15/2022 |
5/2/2022 |
Full |
4/25/2022 |
5/10/2022 |
| CC |
4/20/2022 |
3/5/2022 |
Light |
3/10/2022 |
4/8/2022 |
| CC |
4/20/2022 |
3/10/2022 |
Light |
4/13/2022 |
4/8/2022 |
| CC |
4/20/2022 |
4/15/2022 |
Full |
4/25/2022 |
5/10/2022 |
| CC |
4/20/2022 |
4/20/2022 |
Light |
3/10/2022 |
4/8/2022 |
| DD |
2/15/2022 |
3/5/2022 |
No |
4/13/2022 |
4/8/2022 |
| DD |
2/15/2022 |
3/10/2022 |
Light |
4/25/2022 |
5/10/2022 |
| DD |
2/15/2022 |
4/15/2022 |
Light |
4/13/2022 |
4/8/2022 |
| DD |
2/15/2022 |
4/20/2022 |
Full |
4/25/2022 |
5/10/2022 |
| EE |
4/22/2022 |
3/5/2022 |
No |
3/10/2022 |
4/8/2022 |
| EE |
4/22/2022 |
3/10/2022 |
Light |
4/13/2022 |
5/10/2022 |
| EE |
4/22/2022 |
4/15/2022 |
Light |
4/25/2022 |
4/8/2022 |
| EE |
4/22/2022 |
4/20/2022 |
Full |
5/2/2022 |
4/8/2022
|
The cut date range is between 4/1/2022 and 4/30/2022
- The first KPI is a distinct count of codes which purchase date is between the cut date range: 3 (AA, BB, EE)
(Count(distinct{$<purchasedate = {">=$(=min(CutDate))"},purchasedate = {"<=$(=max(CutDate))"}>} Code))
- The second KPI is a distinct count of code between cut date WHICH last updated date (between cut date) is the date of status full: 2 (AA, EE)
(Count(distinct
{$<purchasedate = {">=$(=min(CutDate))"}
,purchasedate = {"<=$(=max(CutDate))"}
>}
if(
aggr(max(
if((LastUpdate < '$(=max(CutDate)+1)')
, LastUpdate))
,Code) =
aggr(max(
if((LastUpdate < '$(=max(CutDate)+1)'
and Status = 'FULL')
, LastUpdate))
,Code)
, Code)
))
- The third KPI is a Distinct count of code between cut date, WHICH last updated date (between cut date) is the date of status full, AND for last service updated date (between cut date) take the term end of that record and compare it with the cut date (greather than): 1 (should be only AA)
The formula of the third KPI is which I don't know what else I need to add it no make the formula work, I tried adding the following with the function FirstSortedValue
and aggr(FirstSortedValue(distinct termend, - if((LastServiceUpdated< '$(=max(CutDate)+1)' ), LastServiceUpdated)),
Code) >= '$(=max(CutDate))'
What and I doing wrong?
Thanks for your help
