How to create a script priority flag based on today's date?

[QlikV1]

Hi,

I have an incident management dashboard and would like to create a new priority flag/field (Low, Medium, High) based on the incident state and today's date. I already use count calculations in the front-end to create my Low, Medium, High expressions (see below), however, need to translate this into script (back-end) language.

Priority Calculations:

Low:  =Count({$<incident_state={'New','In Progress'},opened_at={">$(=date(Today()-1))"}>}number)

Medium: =Count({$<incident_state={'New','In Progress'},opened_at={">=$(=date(Today()-7))<$(=date(Today()-1))"}>}number)

High: =Count({$<incident_state={'New','In Progress'},opened_at={"<$(=date(Today()-7))"}>}number)

Note: a priority field is already existing, however, currently not used. A new field could be created or the existing values in the priority field overwritten.

[Taoufiq_Zarra]

@QlikV1  can you share a sample data and the expected output ?

[QlikV1]

was just working on it..column A, B & C are already in the application. Column D (priority) is to be generated (dynamically) based on current dates/incident states. See below example

A	B	C	D
number	Incident state	opened_at	priority
SINC0079884	New	Today 05-NOV-20	Low
SINC0081102	In Progress	Yesterday 04-NOV-20	Low
SINC0093197	In Progress	03-NOV-20	Medium
SINC0094047	New	03-NOV-20	Medium
SINC0094325	New	02-NOV-20	Medium
SINC0094812	In Progress	29-Oct-20	Medium
SINC0095103	In Progress	28-Oct-20	High
SINC0095659	In Progress	20-Oct-20	High
SINC0096096	In Progress	20-Oct-20	High
SINC0096186	New	21-Oct-20	High
SINC0096685	New	19-Oct-20	High

Please let me know if you need anything else

[QFabian · Accepted solution]

Hi, create a new field, with the difference between dates :

today() - opened_at  as Days

and then calculate the Priority using that new field

example : 

Load
    *,
    if(Days>7, 'High',
   if(Days >= 1 and Days<=7, 'Medium', 'Low')) as Priority;

LOAD

    number,
   [Incident state],
   opened_at,
   today() - opened_at  as Days
FROM your source;

This is load precedent feature!!

[QlikV1]

Thanks, QFabian! Much appreciated!