Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have to make three kpi using start date and end date
frist kpi count those event end between 30 from today
and second kpi count the event end between 31 to 60 day
thrid count kpi ends between 61 to 90 days
Hi Siddhanttomar,
Supposing you want to count only the event that have already begun,
using data looking like the following:
Table Test
load * Inline [
Start_Date, End_Date, Id
05/03/2022, 05/25/2022, a
05/03/2022, 06/04/2022, b
05/03/2022, 07/04/2022, c
05/05/2022, 05/25/2022, d
05/05/2022, 06/04/2022, e
05/05/2022, 07/04/2022, f
]
try this:
KPI 1
Count({<[Start_Date]*={"<$(=TODAY())"}, [End_Date]*={"<=$(=TODAY()+30)"}>} Id)
KPI 2
Count({<[Start_Date]*={"<$(=TODAY())"}, [End_Date]*={">$(=TODAY()+30)<=$(=TODAY()+60)"}>} Id)
KPI 3
Count({<[Start_Date]*={"<$(=TODAY())"}, [End_Date]*={">$(=TODAY()+60)<=$(=TODAY()+90)"}>} Id)
Partially correct,
1) any + - operation with a date field will return a numeric value, so it needs to be formatted back
2) Always base calculation on Max(Date) available in dataset
Last 30 Days
Count({<[Start_Date]={">$(=Date(Max(Date)-30,'YYYY-MM-DD'))"}>} Id)
60-31 Days
Count({<[Start_Date]={">$(=Date(Max(Date)-61,'YYYY-MM-DD'))<=$(=Date(Max(Date)-30,'YYYY-MM-DD'))"}>} Id)
90-61days
Count({<[Start_Date]={">$(=Date(Max(Date)-91,'YYYY-MM-DD'))<=$(=Date(Max(Date)-60,'YYYY-MM-DD'))"}>} Id)
replace YYYY-MM-DD with the date format of your date field