Set analysis issue

[florent1]

Hello fellows Qlikers,

I have a problem with a set analysis, and any help would be appreciated to solve it.

To give you a context, my main table is a product table joined with a list of warehouses:

product	warehouse
A	1
A	2
B	1
B	2

At those, I left keeped a single table of logistic movements (pickings and receptions) to be done with planned date, warehouse and quantities.

My goal with the set analysis is, by article, find the quantity that has to be picked before the next reception occurs. 

Here it goes :

Sum({$<
operation_type={'picking_operation'}
,[date_planned]={"<$(=Min({$<operation_type={'reception'}>} distinct TOTAL <warehouse,product> [date_planned]))"}>} [quantity])

It works perfectly fine, except that my set analysis within the set analysis (= what I would like to be the minimum next date of reception) is giving me the minimum next date of reception of all my selections, and not line by line.

Even in the preview of the set analysis, it shows only one value, which is the minimum of all my selections : 

And if I apply other selections :

Could you please help me figure it out ?

Have a nice day,

Flo

[tresesco]

---

is giving me the minimum next date of reception of all my selections, and not line by line.

And this is how set analysis works; it calculates once for a chart/object and NOT line by line. Try to replace your set analysis with 'if' statement instead.

[florent1]

Hello Tresesco, 

First of all, thank you for your answer. But I don't understand.
Every set analysis that I made works line by line. Which even allows me to add dimensions if I want to and dig deeper in the analysis if needed.

Here, it is only the set within the set which does not work properly. Maybe I did not explain the problem properly ?

[tresesco]

Say for example, you have a chart with dimension Year and you want to get the sales value for max date for every year. And try to achieve this like this:

Dim : Year

Exp : Sum({<Date={'$(=Date(Max(Date)))'}>}Sales) 

Guess what, we would see sales for single year (i.e. - max year) and single date (max date of the max year). And I was taking about this behavior of set analysis, your set expression would not fetch max dates for every rows (year here). 

Coming back to your issue, try to share a sample app explaining the expected output in that context; we will give a try.

[florent1]

That is exactly my issue, indeed.

Here is an exemple attached. 
I put a sample of the table I would like in first. And in second the data in a simple table.

Here is the output I'm trying to reach :

warehouse	product	qty to pick	qty to receive	RESULT NOW	RESULT NEEDED
warehouse1	productA	400	100	200	(200+100=)300
warehouse2	productA	500	500	0	(0=)0
warehouse1	productB	450	200	0	(150=)150
warehouse2	productB	400	750	0	(400=)400

To explain the result I am getting now but doesn't fit my need (200) :
- the minimun date of reception throughout the data is 12/05/2020
- I only have 1 picking which is before that date, on productA / warehouse1, for 200 quantities the 10/05/2020

To explain the result needed for the 1st line (300) : 
- I have 3 pickings (planned the 10/05 - 19/08 - 19/09)
- but only 2 before the next reception (which occurs 28/08)
- the quantity of those 2 pickings is 200 (planned the 10/05) + 100 (planned the 19/08)

Is it clearer like that ?

Thanks,

Flo 

[florent1]

Hi, 

Does someone have a hint of what to do to achieve my goal ?

I'd be very grateful, thank you 🙂

Flo

[tresesco · Accepted solution]

Try this:

Sum(if(operation='picking' and date_planned < Aggr(nodistinct only(if(operation='reception', date_planned)),warehouse,product), qty))

---

[florent1]

Wonderful ! Thanks a lot @tresesco , it works perfectly 🙂