Front End Expression count if?

[QlikV1]

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!

[Kushal_Chawda · Accepted solution]

@QlikV1  try below

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

[QlikV1]

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

[QlikV1]

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

[Kushal_Chawda · Accepted solution]

@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)

[QlikV1]

You are amazing, thanks!!