Displaying todays ask

[chrispatel]

Hello, 

I have an excel that contains a timeline for a project. It contains the task name, start date, end date, duration, status. I want to create a KPI that shows the task that is happening today. I have tried multiple things but nothing worked. I also want to create a KPI for upcoming task, and incomplete tasks. 

Any help would be great. 

Thank you so much! 

[sidhiq91 · Accepted solution]

@chrispatel  Please see below the sample data that I have loaded to Qlik and then followed the expression written in the front end of.

NoConcatenate
Temp:
Load TaskName,
Date(Date#(Startdate,'MM/DD/YYYY'),'MM/DD/YYYY') as Startdate,
Date(Date#(enddate,'MM/DD/YYYY'),'MM/DD/YYYY') as enddate,
Duration,
Status

inline [
TaskName, Startdate, enddate, Duration, Status
T1,07/01/2022,07/03/2022, 2, Completed
T2,07/04/2022,07/05/2022, 2, Completed
T3,07/10/2022,07/13/2022, 3, InProgress
T4,07/09/2022,07/21/2022, 10, Incomplete
T5,07/10/2022,07/15/2022, 5, Completed
T6,07/16/2022,07/19/2022, 3, Completed
T7,07/27/2022,07/29/2022, 2, Inprogress
T8,07/27/2022,07/30/2022, 3, Inprogress
T9,07/28/2022,07/30/2022, 2, Incomplete
T10,07/29/2022,07/30/2022, 1, Incomplete
T11,07/30/2022,07/30/2022, 1, Incomplete
];

Exit Script;

Now the Expression in the Front end:

Today's Task count: Count({<Startdate={"$(=Date(Today(),'MM/DD/YYYY'))"}>}TaskName)

Future Task Count: Count({<Startdate={">$(=Date(Today(),'MM/DD/YYYY'))"}>}TaskName)

Incomplete Task Count: Count({<Status={'Incomplete'}>}TaskName)

If this resolves your issue please like and accept it as a solution.

[fallenangel6]

if im understanding it right then you want to see how many tasks are ongoing today.

my data is

data:
LOAD
name,
startdt,
enddt,
duration,
status
FROM [lib://Downloads/Sample Data.xlsx]
(ooxml, embedded labels, table is status);

temp:
Load floor(min(startdt)) as mindt,
floor(max(enddt)) as maxdt
resident data;

let vmin=peek('mindt',0,temp);
let vmax=peek('maxdt',0,temp);

cal:
Load date($(vmin)+IterNo()-1) as newdt
AutoGenerate(1)
while($(vmin)+IterNo()-1 <= $(vmax));

join (data)
intervalmatch(newdt) load startdt,enddt resident data;

and in kpi, i used 

=count({<newdt={'$(vtoday)'}>}name)

[chrispatel]

Hello, 

Thanks for the reply! When I add this to the frontend it give me a result of 0.00. Not sure why this is happening. 

[chrispatel]

my backend:

LOAD
"Task Name",
"Assigned To",
Status,
Duration,
"Start",
Finish,
Comments
FROM [lib://Qlik/CTE PHANE PLAN CLEAN.xlsx]
(ooxml, embedded labels, table is Sheet1);

[Frank_S]

@sidhiq91

Your graceful solution works perfectly!

I inserted the load script to a new app.

Then using the #1KPI chart object, inserted the two separate count expressions and received the information below.

cc @chrispatel

[chrispatel]

would there be a way to display the text of the actual task? For instance, Today's task is to go shopping and clean the house. Instead of seeing "2" i would want to see "Go shopping" and "clean the house"