Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
@QlikV1 try below
Count({$<INCIDENT_STATE={'New','In Progress'},CREATED_DATE={">=$(=date(Today()-1))<=$(=date(Today()))"}>}INCIDENT_NUMBERS)
@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 try below
Count({$<INCIDENT_STATE={'New','In Progress'},CREATED_DATE={">=$(=date(Today()-1))<=$(=date(Today()))"}>}INCIDENT_NUMBERS)
Awesome! Works like a charm and I understand what I did wrong : )
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
@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)
You are amazing, thanks!!