Kpi with condition

[Siddhanttomar]

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 

[Pierremouth_]

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)

[vinieme12]

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