Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Set analysis issue

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 :

Have a nice day,

Flo

Labels (1)
• ### Set Analysis

1 Solution

Accepted Solutions
MVP

Try this:

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

7 Replies
MVP

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.

Contributor III
Author

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 ?

MVP

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.

Contributor III
Author

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

Contributor III
Author

Hi,

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

I'd be very grateful, thank you 🙂

Flo

MVP

Try this:

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

Contributor III
Author

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