Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Siddhanttomar
Contributor
Contributor

Kpi with condition

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 

 

2 Replies
Pierremouth_
Contributor II
Contributor II

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
Champion III
Champion III

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

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.