Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
florent1
Contributor III
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:

productwarehouse
A1
A2
B1
B2

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 : 

florent1_1-1597907844609.png

florent1_2-1597907905760.png

 

And if I apply other selections :

florent1_3-1597907945702.png

florent1_4-1597907960727.png

 

Could you please help me figure it out ?

Have a nice day,

Flo

 

 

 

Labels (1)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

Try this:

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


tresesco_0-1598254798510.png

 

View solution in original post

7 Replies
tresesco
MVP
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.

florent1
Contributor III
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 ?

tresesco
MVP
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.

florent1
Contributor III
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 :

warehouseproductqty to pickqty to receiveRESULT NOWRESULT NEEDED
warehouse1productA400100200(200+100=)300
warehouse2productA5005000(0=)0
warehouse1productB4502000(150=)150
warehouse2productB4007500(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
Contributor III
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

tresesco
MVP
MVP

Try this:

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


tresesco_0-1598254798510.png

 

florent1
Contributor III
Contributor III
Author

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