Need a column value based on other columns

[qlikuser2023]

I've the below table

TYPE	START_DATE	END_DATE	DE_ID	DE_LEN
IN	3/1/2023	3/1/2023	1	10
IN	3/2/2023	3/29/2023	2	23
OUT	3/4/2023	3/20/2023	3	15
OUT	3/4/2023	3/21/2023	3	20
IN	3/2/2023	4/1/2023	4	20
OUT	1/2/2023	4/1/2023	3	21

I need to get the total del_len for de_id for type = 'out' and de_id is between 3/1/2023 and 3/30/2023

I tried this but not getting incorrect data:

SUM( distinct if(MasterDate >= DATE(START_DATE,'MM/DD/YYYY') and MasterDate <= DATE(END_DATE,'MM/DD/YYYY') and TYPE = 'OUT', IF(DE_ID, DE_LEN)) )

MasterDate is from the date picker 

[BrunPierre]

Like this, perhaps.

SUM( distinct Aggr( if(MasterDate >= DATE(START_DATE,'MM/DD/YYYY') and MasterDate <= DATE(END_DATE,'MM/DD/YYYY') and TYPE = 'OUT', DE_ID), DE_LEN) )

[qlikuser2023]

Nope, not working. It's returning 0.

I'm able to get a count of DE_IDs using this:

COUNT(DISTINCT {<TYPE={'OUT'}>} if(MasterDate >= DATE(START_DATE,'MM/DD/YYYY') and MasterDate <= DATE(END_DATE,'MM/DD/YYYY'), DE_ID))

so I need the sum(DE_LEN) for all those DE_IDs

[BrunPierre]

Does this not work with the same parameters?

COUNT Sum(DISTINCT {<TYPE={'OUT'}>} if(MasterDate >= DATE(START_DATE,'MM/DD/YYYY') and MasterDate <= DATE(END_DATE,'MM/DD/YYYY'), DE_ID DE_LEN))

[qlikuser2023]

No, since I cannot do a distinct DE_LEN (this will cause some values to drop off).  The values could be like this

DE_ID	DE_LEN
12	10
10	10
11	20
13	11

I need all the DE_LEN for all DE_ID

[BrunPierre · Accepted solution]

Create a variable for these DE_IDs
LET vDE_IDList = 'Concat(DISTINCT {<TYPE={'OUT'}>} if(MasterDate>= DATE(START_DATE,'MM/DD/YYYY') and MasterDate <= DATE(END_DATE,'MM/DD/YYYY'), DE_ID,', '))' ;

And use it within the expression
=SUM({<DE_ID={$(vDE_IDList)}>} DE_LEN)

[qlikuser2023]

Thank you. This worked.