counting days beetwen two dates and problem with measures

[kubafox]

I have two tables:

1:

send date	tracking number	fowarder
2022-01-01	123123	UPS
2022-01-02	456465	DHL
2022-01-06	789789	DPD

2:

delivery date	tracking number
2022-01-02	123123
2022-01-03	456465
2022-01-08	789789

in the script:

1_tmp:
LOAD
send date,
tracking number,
fowarder,

FROM [*qvd]
(qvd)


1:
NoConcatenate
LOAD
send date,
tracking number,
fowarder,
FROM [*.qvd]
(qvd)
drop table 1_tmp;

2:
Left keep (1)
LOAD

tracking number,
delivery date
FROM [1.xlsx]
(ooxml, embedded labels, table is x);


LOAD
tracking number,
delivery date
FROM [2.xlsx]
(ooxml, embedded labels, table is z)

now i count days between days by and its work fine:

(NetWorkDays([send date],delivery date))

and now i need to count how many tracking numbers was delivered in time (two days count) i try this thing:

Aggr( Count(NetWorkDays([send date],delivery date)=2), fowarder)

 but results is wrong.

anyone can help me? 😉

[lironbaram · Accepted solution]

Hi, 
you can try this 

sum(Aggr( if(NetWorkDays([send date],delivery date)<=2,1,0),[tracking number], fowarder))

[kubafox]

thank you!