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
Did you mean:
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

Labels (13)

• Visualization

2 Replies
Contributor II

Hi Siddhanttomar,

Supposing you want to count only the event that have already begun,

using data looking like the following:

Table Test

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)

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.
Tags
Community Browser