determine the number of active vehicles

[schuffe]

Hello everyone,

i prepared some data to explain my problem:

My goal is to determine how many vehicles where active on each day. (day  >= tour start &&  day <= tour end)

For each day the number of active vehicles should look like this:

I tried to do this in Qlik with the following expression:

Sum(aggr(Sum(If(Dates >= [tour start] AND Dates <= [tour end],1,0)),vehicle)) 

That gives me a total of 17 active vehicles. (Too much)

I know what the formula is doing wrong but I dont know how to fix it.

In this case:

'A' should only count as one active vehicle for 02.01.2021. But my formula counts 'A' twice.

Does anyone know, how to adjust the formula.

I think I should add something like

count(distinct( vehicle + Date))

Thanks in advance 🙂

[PradeepK]

Move your logic in Script.. Create 'Active Flag' field.. Try to avoid Aggr() as much as possible.

Code for reference only

//Add calculated field in script
If(Dates >= [tour start] and Dates <= [tour end],1,0) as Active_Flag

//IN expression
count(distinct {< Active_Flag = {1} >} vehicle)

[Ksrinivasan · Accepted solution]

hi,

try this script

vehiv:
LOAD
Datee,
Vehicle,
"start date",
"end date"
FROM [lib://REPORT EXTRACTION SSSS1.xlsx]
(ooxml, embedded labels, table is vehiv);
RESULT:
Load Distinct
(Vehicle&'-'&SDate) as DDTT,
SDate,
Vehicle;
LOAD
Vehicle,
Date(Date([start date]+iterno()-1),'DD-MM-YYYY') as SDate
resident vehiv while [start date]+IterNo()-1 <=[end date];
drop table vehiv;

Result:

3hrs to reach.

Ksrinivasan

[schuffe]

Thank You so much.

I really appreciate your work and effort 🙂

[Ksrinivasan]

you are welcome Mr. schuffe.

ksrinivasan

[schuffe]

Hello again 🙂

I got another question. I think it is very simple but I'm not aible to do it the right way. How would this script look like if the connection isn't an excel file anymore ? My App is now connected to an oracle server and I changed the script like this:

vehiv:
LOAD
[Datee],

[Vehicle],
[start date],
[end date];

SELECT

"Datee",
"Vehicle",
"start date",
"end date"
FROM "..."."table_name";

RESULT:
Load Distinct
(Vehicle&'-'&SDate) as DDTT,
SDate,
Vehicle;
LOAD
Vehicle,
Date(Date([start date]+iterno()-1),'DD-MM-YYYY') as SDate
resident vehiv while [start date]+IterNo()-1 <=[end date];
drop table vehiv;