topic Front End Expression count if? in QlikView

Front End Expression count if?

QlikV1 — Fri, 15 Nov 2024 23:44:04 GMT

Hi All,

I am working with data from a service management tool. How can I count specific incident numbers?

Goal: Count INCIDENT_NUMBERS if INCIDENT_STATE = 'New' or 'In Progress' AND CREATED_DATE = TODAY OR CREATED_DATE = TODAY -1

Note: expression must be dynamic and not the dates hardcoded

Attempts:

1) =if(CREATED_DATE>=today()-1 or CREATED_DATE<=today()-1,count({<INCIDENT_STATE='New','In Progress','On Hold'}>} INCIDENT_NUMBERS)

2) =Count({$<INCIDENT_STATE={'New','In Progress'},CREATED_DATE{>=Today()-1<=Today()}>}INCIDENT_NUMBERS)

Thank you for your time, it's much appreciated!

Re: Front End Expression count if?

Kushal_Chawda — Tue, 27 Oct 2020 15:40:11 GMT

@QlikV1 try below

Count({$<INCIDENT_STATE={'New','In Progress'},CREATED_DATE={">=$(=date(Today()-1))<=$(=date(Today()))"}>}INCIDENT_NUMBERS)

Re: Front End Expression count if?

QlikV1 — Tue, 27 Oct 2020 15:51:30 GMT

Awesome! Works like a charm and I understand what I did wrong : )

Re: Front End Expression count if?

QlikV1 — Tue, 27 Oct 2020 16:49:00 GMT

As a follow-up question, if I want the same expression but a different date range (still calculated from today's date), would I simply change the "today()" & "today()-1"?

I tried to change the values but the numbers don't add up.

Eventually, I need to count incidents for:

- today and yesterday (formula above)

- last 7 days (not including today and yesterday)

- everything in CREATED_DATE not including the last 7 days

Re: Front End Expression count if?

Kushal_Chawda — Tue, 27 Oct 2020 16:56:46 GMT

@QlikV1 may try below

- last 7 days (not including today and yesterday)

Count({$<INCIDENT_STATE={'New','In Progress'},CREATED_DATE={">=$(=date(Today()-7))<$(=date(Today()-1))"}>}INCIDENT_NUMBERS)

- everything in CREATED_DATE not including the last 7 days

Count({$<INCIDENT_STATE={'New','In Progress'},CREATED_DATE={"<$(=date(Today()-7))"}>}INCIDENT_NUMBERS)

Re: Front End Expression count if?

QlikV1 — Tue, 03 Nov 2020 12:47:26 GMT

You are amazing, thanks!!