Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
(Count(distinct{$<purchasedate = {">=$(=min(CutDate))"},purchasedate = {"<=$(=max(CutDate))"}>} Code))
(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 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
hopefully this leads to a solution.
this expression will tell you the number of code where the last upadted date status = Full
=Count(distinct {<Code={"=aggr(FirstSortedValue(Status, -[Last updated]),Code)='Full'"}>} Code)