A set analysis for each record

[jmmayoral3]

I have a table of item sales with their date, units sold and a field that indicates whether there was a stockout that day or not. (1, there was, 0 there was not)
The user can choose a date range and I should get a table chart with 2 columns: the item and the number of out-of-stock days after the first sale date within the chosen range.

LOAD * INLINE [
item, day, sales, stockout_day
A , 01/04/2023, 0 , 1
A , 02/04/2023, 0 , 1
A , 03/04/2023, 20 , 0
A , 04/04/2023, 3 , 0
A , 06/04/2023, 0 , 1
A , 07/04/2023, 0 , 1
A , 08/04/2023, 5 , 0
A , 09/04/2023, 0 , 0
A , 10/04/2023, 3 , 1
B , 01/04/2023, 3 , 0
B , 02/04/2023, 0 , 1
B , 03/04/2023, 0 , 1
B , 04/04/2023, 0 , 1
B , 06/04/2023, 0 , 1
B , 07/04/2023, 0 , 1
B , 08/04/2023, 0 , 1
B , 09/04/2023, 3 , 0
B , 10/04/2023, 0 , 0
];

Note: Date format: DD/MM/YYYY

Assuming that the user chooses from 04/04/2023 to 04/10/2023 (both included), the result should be:

item   #out-of-stock days
A                      3
B                      0

In the case of item A there are several sales dates, but the smallest within the period is 04/04/2023. From there we count the days with stock out until 04/10/2023 (end date of the period selected by the user) and the count is 3.

In the case of item B, the date with the smallest sales within the period is 09/04/2023 (01/04/2023 is outside the period) and from there to the end there is no day of stockout, so the count is 0.

I am using this expression for counting:
sum({<day={">=$(=MIN({<sales={">0"}>} day))"}>} stockout_day)

But the result it returns is:
item          # out-of-stock days
A                                 3
B                                 4

The Set analysis returns the lowest sales date of the 2 items together (that is, 04/04/2023), not the lowest of each of them, and since that day item B has had 4 days of stock out , which is wrong because it should be 0.

How can it be solved?
How to make it take the minimum date of each article so that it gives the correct result?

Thank you

[vincent_ardiet_]

You are using an expansion formula $(=...), this is executed at a global level before evaluating the rest of the expression. 
So this is equivalent to: sum({<day={">=04/04/2023"}>} stockout_day)
Try like this:
sum({<day={"=day>=MIN({<sales={[>0]}>} day)"}>} stockout_day)

[jmmayoral3]

Thank you for your fast answer, but it doesn't work.

When I selected days from 4th march to 10th march (both included) , It returns

item          # out-of-stock days
A                                 1
B                                 2

If I select item A, then # out-of-stock days = 1, but if I select item B, then # out-of-stock days = 0

The  # out-of-stock days changes depending on if I select item A, item B or none. This indicates that the expression continues calculating something for both items at same time.

[jmmayoral3 · Accepted solution]

I opened this topic in the channel "Qlik en español" and @rubenmarin send me this solution.

It works properly.

sum(aggr(If(dia>=Min(TOTAL <articulo> {<ventas={">0"}>} dia), dia_rotura_stock),articulo,dia))